Listener Password

The oracle listener can be the object of attacks from someone willing to take control of the database.

Putting a password on the listener is easy.

C:\>LSNRCTL

LSNRCTL>status

If you got some instances described then this is your current listener. The default name is listener and a potential hacker is well aware of that.

It’s good to create the listener with a different name.

If you don’t know your listener’s name, go to the services and look for OracleListenerSomelistenername. Your listener name is Somelistenername.

LSNRCTL>change_password Somelistenername

old password: There was nothing so we just press enter.

new password: YourNewPassword

retype new password: YourNewPassword

LSNRCTL>save_config

The save_config command will add the password encrypted in the listener.ora file located in ORACLE_HOME\NETWORK\Admin

If that command haven’t been performed then no password have been added.

How to check expdp progress

V$SESSION_LONGOPS :This view helps determine Data Pump export progress indicator through the MESSAGE column.

select t.username,t.START_TIME,t.LAST_UPDATE_TIME,t.TIME_REMAINING,t.ELAPSED_SECONDS,
t.opname,t.target_desc,t.sofar,t.totalwork,t.message from V$SESSION_LONGOPS t where/* t.USERNAME = 'user_name' and*/ t.TARGET_DESC = 'EXPORT'

Database Hanging due to Row lock contention

To check for the sessions waiting, use the query

select sid, username, event, blocking_session,
seconds_in_wait, wait_time
from v$session where state in ('WAITING')
and wait_class != 'Idle';

To check for the corresponding sql statement relating the waiting session

select sid, sql_text
from v$session s, v$sql q
where sid in (254,270)
and (
q.sql_id = s.sql_id or
q.sql_id = s.prev_sql_id);


Wait event Class
select wait_class_id, wait_class,
total_waits, time_waited
from v$session_wait_class
where sid = 270;

From the above query find the highest wait class and find the cause of the wait by using the query below.

select event, total_waits, time_waited
from v$system_event e, v$event_name n
where n.event_id = e.event_id
and wait_class_id = xyz; -- Where xyz is the highest wait_class_id from the previous query

How to Calculate Table size

TABLE not partitioned:
/* Size of each table in a user schema */

Connect to the user and use the following query.

Table not partitioned

select segment_name, t.tablespace_name, bytes / 1024 / 1024 MB from user_segments t
Where t.segment_type = 'TABLE' order by 3 desc


Alternatively to check what are all the objects stored in a tablespace.

select segment_name, t.tablespace_name, t.segment_type,bytes / 1024 / 1024 MB from user_segments t Where t.tablespace_name = 'DCSTAB' order by 4 desc


For a Partitioned table


select segment_name, partition_name, bytes / 1024 / 1024 MB
from user_segments Where segment_type = 'TABLE PARTITION' and Segment_Name= and partition_name = ;

TABLE partitioned:
select segment_name, sum(bytes / 1024 / 1024) MB
from user_segments Where segment_type = 'TABLE PARTITION'
and Segment_Name= Group by segment_name;

Current Runing SQL statements

To have a script that displays all of the currently executing SQL statements - use the following Query in case if you are experiencing performance problem.

--Currently Executing SQL statements.
select u.sid,substr(u.username,1,12) user_name,s.sql_text from v$sql s, v$session u
where s.hash_value = u.sql_hash_value and sql_text not like '%from v$sql s, v$session u%'
order by u.sid


The Sample output is given below


SID USER_NAME SQL_TEXT
7 184 PROC_DEV SELECT A.* FROM MAIN_HEADER A INNER JOIN
VIEW_HEADER_N2 B ON A.MESSAGE_NUMBER =
B.MESSAGE_NUMBER WHERE A.EPROC_SOURCE = :B1 AND
B.SALES_TOTAL_AMOUNT != 0 AND B.PO_CLOSED_DATE IS
NULL AND A.MESSAGE_NUMBER IN (SELECT DISTINCT
DT.MESSAGE_NUMBER FROM VIEW_DETAIL_N2 DT INNER JOIN
VIEW_DELIVERY_N2 DL ON DT.MESSAGE_NUMBER =
DL.MESSAGE_NUMBER) ORDER BY A.MESSAGE_NUMBER

8 184 PROC_DEV SELECT A.* FROM MAIN_HEADER A INNER JOIN
VIEW_HEADER_N2 B ON A.MESSAGE_NUMBER =
B.MESSAGE_NUMBER WHERE A.EPROC_SOURCE = :B1 AND
B.SALES_TOTAL_AMOUNT != 0 AND B.PO_CLOSED_DATE IS
NULL AND A.MESSAGE_NUMBER IN (SELECT DISTINCT
DT.MESSAGE_NUMBER FROM VIEW_DETAIL_N2 DT INNER JOIN
VIEW_DELIVERY_N2 DL ON DT.MESSAGE_NUMBER =
DL.MESSAGE_NUMBER) ORDER BY A.MESSAGE_NUMBER


If you could tune these statement most of the Performance related problems will be solved.



===================================================

Flash Back until a specified time

This article will help you recover a table upto a specified time. This Procedure was tested on Oracle 10g Release 2 Firstly create a test table which will be used for testing

Create table Emp_new as select * from emp where 1=2;

Insert some values and issue a commit.
Now select the systimestamp from the database.

Select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
09-Nov-09 07.16.47.906882 PM +08:00

Now delete data from the Emp_new table and commit it.

Delete from emp_new;
Commit;

Now select the systimestamp again from the database.
select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
09-Nov-09 07.23.47.906882 PM +08:00

Now you know that table was truncated at 7:16. So we will perform recovery / Flashback till 3:11

INSERT INTO emp_new
(SELECT * FROM emp_new AS OF TIMESTAMP TO_TIMESTAMP('09-NOV-09 19:16:00','DD-MON-YY HH24: MI: SS'))

Commit;

Now you can see the data has been restored in the table.

SQL> Select * from emp_test;

Existing Index Needed or Not needed

/* In Oracle10g we can easily see what indexes are used, when they are used and the context where they are used. Here is a simple AWR query to plot index usage:*/

Connect as sys user and use this query to check how many times each index is used for a schema...


Method 1 : ( Oracle 10g )
select p.object_owner owners, p.object_name Obj_Name, p.operation Operation, p.options Options, count(1) Idx_Usg_Cnt
from dba_hist_sql_plan p,dba_hist_sqlstat s
where p.object_owner = 'Schema_Name' and p.operation like '%INDEX%' and p.sql_id = s.sql_id
group by p.object_owner,p.object_name,p.operation,p.options
order by 1,2,3

To list all the indexes in a schema use the following query.

select t.owner,t.table_name,t.index_name,t.last_analyzed from dba_indexes t
where t.owner = 'schema_name'



/* -- Here is another script for tracking unused indexes and showing the invocation count(How manytimes and when they are called/Used) of all indexes.
-- Best of all, this script shows the columns referenced for multi-column indexes: */

select to_char(sn.begin_interval_time,'yy-mm-dd hh24') Begin_Int_Time, p.search_columns Search_Col, count(*) Invocation_Cnt
from dba_hist_snapshot sn, dba_hist_sql_plan p, dba_hist_sqlstat st
where st.sql_id = p.sql_id and sn.snap_id = st.snap_id and lower(object_name) like /*lower('%&idxname%')*/ lower ('Idx_Name')
group by begin_interval_time,search_columns

With these queries we can find out the unused indexes and drop them off.


Method 2 : (Oracle 9i)
Connect to a user and then
alter index index_Name monitoring usage

then use the query below to check whether the index is used

select index_name mon,used from v$object_usage;

Here is a simple SQL*Plus script to track all index usage in a Oracle schemas:

set pages 999;
set heading off;
spool Idx_monitor.sql

select 'alter index '||owner||'.'||index_name||' monitoring usage;'
from dba_indexes
where owner = 'Schema_name';

spool off;

@Idx_monitor.sql

Once idx_monitor.sql completes, you can check for the Index usage by

select index_name mon,used from v$object_usage;