How to enable sql session tracing in oracle database

Enabling session tracing in oracle database


1. Identify sql client session to be traced


For example you are planning to trace the session for module ABC , identify the SPID for session e.g. 13762780

alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
set line 1000
col OSUSER for a10
col SID for 999
col USERNAME for a20
col MACHINE for a20
set pagesize 100
SELECT distinct
a.sid,
a.serial#,
b.spid, sq.sql_id,
b.pid,
a.username,
a.machine,
a.status,
a.module,
a.osuser,
a.LAST_CALL_ET/60 "Inac_Min",a.logon_time,c.event, sq.sql_text
FROM
v$session a,
v$process b,
v$session_wait c,
v$sqlarea sq
WHERE
a.username IS NOT NULL
AND
a.paddr=b.addr
and a.username='GENEVA_ADMIN' and a.sql_hash_value=sq.hash_value
and (upper(a.module) like '%ABC%')
and c.sid=a.sid;


2. Enable trace




For example if spid 13762780 has to be traced

oradebug setospid 13762780

oradebug unlimit

oradebug Event 10046 trace name context forever, level 12

oradebug tracefile_name


3. disable trace


Incase session does not get disconnect the session tracing keep on filing trace file which may lead to panic utilization of file system


oradebug setospid 13762780

oradebug Event 10046 trace name context off


4. Analyzing trace file using tkprof


tkprof trace analyzer will extract the trace file generated to showcase execcution plan of each query executed in session


tkprof testdb_ora_13762780.trc tk_testdb_ora_13762780.out explain=test_mis/test_mis123 waits=yes sort=exeela


5. More methods of tracing


Following command can be executed to enable trace for current session tracing


alter session set tracefile_identifier='abc_trace';

alter session set sql_trace=TRUE;

6. More methods of enabling bulk tracing and analyzing trace with condition

In case you need to enable tracing on complete database , below trigger can be created which will trace each session connected on DB till trigger is in enable state

Note: If trigger is in invalid state then it will decline new connection to DB leading to outage


CREATE OR REPLACE TRIGGER trace AFTER logon ON database begin execute immediate 'Alter session set tracefile_identifier=''ABC_traces''';
execute immediate 'Alter session set events ''10046 trace name context forever, level 12'''; end;
/


One trace files are generated in bulk due to trigger you may use below code to search for ABC@TESTLAB002 in trace and generate tkprof for same


for i in `ls -lrt | grep -i ABC_traces.trc | awk '{ print $9 }'`
do
#echo $i
if [ `cat $i | grep -i ABC@TESTLAB002 | wc -l | awk '{ print $1 }'` -gt 0 ] ; then
echo $i
tkprof $i $i.out explain=test_mis/test_mis123 waits=yes sort=exeela
fi;
done