How to Trace a Particular Session ( User ) ?

To Enable trace file for Particular Sessin (User ):

First we have to know spid number,i know to methods to trace a session one method is oradebug and another method execute the file.


Method 1:

SQL>Select a.pid, a.spid, a.username from v$process a ,v$session b where b.paddr=a.addr and sid=429;

       PID             SPID      USERNAME
      ----------    ------------   ---------------
       115             14727        oracle

SQL>oradebug setospid 14727;
    Oracle pid: 115, Unix process pid: 14727, image: oracle@PSCF

SQL>oradebug event 10046 trace name context forever,level 12;
Statement processed.

SQL>oradebug tracefile_name;
Statement processed.

SQL>oradebug unlimit;
/Oracle/app/oracle/admin/RPM12/udump/rpm12_ora_14727.trc

Method 2:

SQL>exec dbms_system.set_sql_trace_in_session(777,143,true);

777 - sid number

143 - serial number

true - enable


To Disable the Trace File:

SQL>oradebug setospid 14727;
    Oracle pid: 115, Unix process pid: 14727, image: oracle@PSCF

SQL>oradebug event 10046 trace name context off;
Statement processed.

Generating Tkprof:

After the trace the particular session we get a trace file but it is a binary format for that resion we are generating the tkprof

tkprof /ofi11i/admin/TESTCE2/udump/testce2_ora_24248572.trc testce2_ora_24248572.txt sort=exeela,fchela,prsela