Saturday, November 06, 2010

Trace a specific session?

Tracing a specific session instead of the entire system allows you to get smaller and very readable tracefiles.

1. Open two sessions - one where you will control the trace and one where you can run the processes to be traced.

2. Connect to the database with dba privileges

SQL> connect / as sysdba
select v$process.SPID, v$process.USERNAME from v$process, v$session where v$session.PADDR=v$process.ADDR order by v$session.logon_time;

--------- ---------------
863 oracle
865 oracle
867 oracle
5104 oracle
5159 oracle

If you run this query just before and after your logon to your other session, you should be able to find the SPID of your connection. Make sure you don't trace the wrong process.

3. Suppose our SPID is 5159.

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

The second command will include the bind variables in the trace file.

4. Perform the actions you want to trace in your other session, then exit server manager. Your tracefile now only contains those statements which are relevant.

Sukhwinder Singh


No comments:

Post a Comment

Note: Only a member of this blog may post a comment.

There was an error in this gadget