Oradebug

Oradebug utility can be used to trace a user session. To trace a session its OS process ID is required which can be obtained by joining v$session and v$process views as shown below:

SQL> select s.username,S.sid,S.serial#,spid

 2  from v$session s, v$process p

 3  where s.username=p.username

 4  and s.paddr=p.addr

 5  and s.username='HNAIK';



USERNAME                              SID    SERIAL# SPID

------------------------------ ---------- ---------- ------------

HNAIK                                 154          4 5548

Now login to the database as SYSDBA: 

C:\Documents and Settings\hnaik> sqlplus / as sysdba



SQL*Plus: Release 10.2.0.1.0 - Production on Thu Oct 29 14:37:40 2009



Copyright (c) 1982, 2005, Oracle.  All rights reserved.





Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

Start the tracing:

SQL> oradebug setospid 5548

Oracle pid: 20, Windows thread id: 5548, image: ORACLE.EXE (SHAD)



SQL> oradebug event 10046 trace name context forever,level 8;

Statement processed.

Stop the tracing:

SQL> oradebug event 10046 trace name context off;

Following command identifies the trace file that is created….

SQL> oradebug tracefile_name

c:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_5548.trc