Thursday, October 29, 2009

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