Log Miner

These are steps to use log miner:

1-) Execute following PL/SQL block as sys user (note that first dbms_logmnr.add_logfile procedure is called with dbms_logmnr.new argument while others are called with dbms_logmnr.addfile argument):

begin
sys.dbms_logmnr.add_logfile (logfilename => '{oracle log file name}',options=>sys.dbms_logmnr.new);
sys.dbms_logmnr.add_logfile (logfilename => '{oracle log file name}',options=>sys.dbms_logmnr.addfile);
...
sys.DBMS_LOGMNR.START_LOGMNR(options => sys.dbms_logmnr.DICT_FROM_ONLINE_CATALOG);
end;

2-) You can select log content using following query:

select * from V$LOGMNR_CONTENTS;

3-) Because content is read from file sequencely, it is very slow. If you are planning to query it multiple times, i strongly suggest you to insert log miner data to a local table and create an index on timestamp column. Requested storage for local table and its index is usually at around 4*(log size)*(number of log files).

create table LOGMNR TABLESPACE TOOLS as select * from V$LOGMNR_CONTENTS;
CREATE INDEX LOGMNR_IX1 ON LOGMNR ("TIMESTAMP") TABLESPACE TOOLS ;

4-) After you finish with log miner, execute following query:

begin
sys.dbms_logmnr.end_logmnr;
end;

How to find remote session executing over a database link

Follow this procedure to find remote session created by local session executing query over database link:

1-) Find local session id from v$session

2-) Execute following script on both local and remote databases:

Select /*+ ORDERED */
s.sid,substr(s.username,1,15),substr(s.ksusemnm,1,10)'-' substr(s.ksusepid,1,10) "ORIGIN",
substr(g.K2GTITID_ORA,1,35) "GTXID",
substr(s.indx,1,4)'.' substr(s.ksuseser,1,5) "LSESSION" ,
substr(decode(bitand(ksuseidl,11),1,'ACTIVE',0,
decode(bitand(ksuseflg,4096),0,'INACTIVE','CACHED'),
2,'SNIPED',3,'SNIPED', 'KILLED'),1,1) "S",
substr(w.event,1,10) "WAITING"
from x$k2gte g, x$ktcxb t, x$ksuse s, v$session_wait w ,v$session s
where g.K2GTDXCB =t.ktcxbxba
and g.K2GTDSES=t.ktcxbses
and s.addr=g.K2GTDSES
and w.sid=s.indx
and w.sid=s.sid
/

3-) From the script output on local database find GTXID value for local session
4-) From the script output on remote database, search for a GTXID value matches the one we find on step 3. When you find it, sid column of the same row is the session we are looking for on remote database.

Another method is using lsof (assuming it is a unix server)

1-) Find process id of the local session

select spid from v$session s ,v$process p where s.paddr=p.addr and s.sid={local_sid}

2-) On the server (running local database) find out TCP connections of the session

lsof -p {process id}

3-) Search for a line containing remote server name (or IP) in output of step 2. When you find it, take destination port number
4-) On remote server run following command to find oracle process listening on this port

lsof -i tcp:{port_number}

5-) Now you can find remote session by running following script on remote database:

select s.sid from v$session s ,v$process p where s.paddr=p.addr and spid={oracle_process_id}

How to determine database segments which are participated in global cache wait events.

Here is a sql script to determine database segments which are participated in global cache wait events.

select /*+ rule */
ash.sql_id, de.owner, de.segment_name, de.segment_type,
ash.event,
sum(ash.time_waited) total_time,
count(*) count,
trunc(sum(ash.time_waited)/count(*)) Avg_wait
from
gV$ACTIVE_SESSION_HISTORY ash,
dba_extents de
where
ash.event like 'gc%' and
ash.P1TEXT='file#' and
ash.P2TEXT='block#' and
ash.p1=de.file_id and
ash.time_waited > 0 and
ash.p2 between de.block_id AND (de.block_id+de.blocks-1)
group by ash.sql_id,de.owner, de.segment_name, de.segment_type, ash.event
order by 6 desc;

How to move/rename a datafile while database is online but tablespace is required to be taken offline

1-) First take tablespace offline to which datafile belongs:

ALTER TABLESPACE MYTABLESPACE OFFLINE NORMAL;

2-) Move datafile to new location

mv /datadir/datafile.dbf /newdatadir/datafile.dbf

3-) Rename datafile name in database

ALTER TABLESPACE MYTABLESPACE RENAME DATAFILE '/datadir/datafile.dbf' TO '/newdatadir/datafile.dbf';

4-) Bring tablespace online:

ALTER TABLESPACE MYTABLESPACE ONLINE;

Searching dba_views giving text condition

If you try to run a query like this:

SQL>select * from dba_views where text like '%some text here%';

following error occurs:

select * from dba_views where text like '%some text here%'
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

You can use following function to search dba_views giving text condition:

create or replace
FUNCTION SEARCH_VIEW_TEXT( in_owner varchar,in_view_name varchar, in_search_str
varchar)
RETURN INTEGER IS
w_crs integer;
r_val integer;
num_bytes INTEGER := 32000;
offset INTEGER := 0;
out_val VARCHAR2(32000);
out_length INTEGER;
begin
w_crs := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(w_crs, 'select text from dba_views where owner=:OWNR and view_name=:VNAME ',dbms_sql.native);
DBMS_SQL.BIND_VARIABLE(w_crs, ':OWNR', in_owner);
DBMS_SQL.BIND_VARIABLE(w_crs, ':VNAME', in_view_name);
DBMS_SQL.DEFINE_COLUMN_LONG(w_crs,1);
r_val := DBMS_SQL.EXECUTE(w_crs);
r_val := DBMS_SQL.FETCH_ROWS(w_crs);
LOOP
DBMS_SQL.COLUMN_VALUE_LONG(w_crs, 1, num_bytes, offset, out_val, out_length);
IF (INSTR(UPPER(out_val),upper(in_search_str))>0) THEN
DBMS_SQL.CLOSE_CURSOR(w_crs);
RETURN 1;
END IF;
offset := offset+num_bytes;
IF out_length < num_bytes THEN
EXIT;
END IF;
END LOOP;
DBMS_SQL.CLOSE_CURSOR(w_crs);
RETURN 0;

EXCEPTION
WHEN OTHERS THEN
IF DBMS_SQL.IS_OPEN(w_crs) then
DBMS_SQL.CLOSE_CURSOR(w_crs);
END IF;
RAISE;
END;

SQL> select * from dba_views where owner not in ('SYS','SYSTEM') and SEARCH_VIEW_TEXT(owner, view_name, 'some text here') = 1

ORA-02449: unique/primary keys in table referenced by foreign keys

When you hit this error, it means that you can not drop(modify) current table (or index) because there are some foreign keys defined on some other tables referencing unique/primary keys of the current table. You should drop these foreign keys in order to drop/modify current table/index.

SQL script to find out these foreign keys:

select * from dba_constraints where R_CONSTRAINT_NAME in (select CONSTRAINT_NAME from dba_constraints where owner='MYOWNER' and table_name='MYTABLE') and CONSTRAINT_TYPE='R';

How to Create Function Based Index

Here is an example of creating function based index setting null values to default values:

CREATE INDEX ERKAN.MYTABLE_FUNC_IX on ERKAN.MYTABLE (COL1, COL2,nvl(COL3,COL1), nvl(COL4,to_date('01/01/2009 00:00:00','DD/MM/YYYY hh24:mi:ss')), nvl(COL5,0))
tablespace MYTABLESPACE;

How to trace session(s)

Using DBMS_SUPPORT package

Starting Trace:
Alter system set max_dump_file_size=unlimited;
exec sys.dbms_support.START_TRACE_IN_SESSION(sid,serial#,waits=>TRUE, binds=>TRUE ) ;

Stopping Trace:
Exec sys.DBMS_SUPPORT.STOP_TRACE_IN_SESSION( sid , null )
Alter system set max_dump_file_size=102400;

Generating Start-Stop Trace script for sessions (trace.sql):

ACCEPT USERNAME char prompt 'Enter the User Name > '
variable b0 varchar2(50)
exec :b0 := upper('&USERNAME');

select '------------ START TRACE ------------' TRACE_SCRIPT from dual
union all
select 'Alter system set max_dump_file_size=unlimited;' TRACE_SCRIPT from dual
union all
select 'exec sys.dbms_support.START_TRACE_IN_SESSION('||sid||','||serial#||',waits=>TRUE,binds=>TRUE ) ;' TRACE_SCRIPT from v$session where username=:b0
union all
select '------------ STOP TRACE ------------' TRACE_SCRIPT from dual
union all
select 'Exec sys.DBMS_SUPPORT.STOP_TRACE_IN_SESSION('||sid||',null);' TRACE_SCRIPT from v$session where username=:b0
union all
select 'Alter system set max_dump_file_size=102400;' TRACE_SCRIPT from dual
/

Using Oradebug

Find server process id of the session:

select spid from v$session s ,v$process p where s.paddr=p.addr and s.sid=&sid

Starting Trace:
sqlplus '/ AS SYSDBA'
SQL> oradebug setospid {spid}
Oracle pid: {pid}, Unix process pid: {spid}, image: oracle@... (TNS V1-V3)
SQL> oradebug event 10046 trace name context forever, level 12
Statement processed.

Stopping Trace:
SQL> oradebug event 10046 trace name context off
Statement processed.

How to create new service name

In order to define a new service name:

1-) Modify service_names parameter and add new service name:
if spfile is used then
alter system set service_names='..., {new_service_name}' scope=both;
if init ora file is used then

• alter system set service_names='..., {new_service_name}';
• modify init ora file and add {new_service_name} to service_names parameter
  

2-) Add following SID description to listener configuration (listener.ora)
SID_LIST_... =
( SID_LIST=
(SID_DESC =
(ORACLE_HOME =...)
(SID_NAME = ...))
....
(SID_DESC =
(GLOBAL_DBNAME={new_service_name})
(SID_NAME = {oracle_sid})
(ORACLE_HOME ={oracle_home}))
)

3-) Restart listener.

Now you may connect to database using following tnsnames.ora configuration:

{tnsname}.WORLD = (DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(Host = {host_name})(Port = {port}))(CONNECT_DATA =(SERVICE_NAME = {new_service_name})))

After login if you query v$session system view, you will see new service name in SERVICE_NAME column for your session.