Donald K. Burleson
Oracle Utilities Tips
ORADEBUG Session Trace
The oradebug utility provides the ability to trace a particular user
session. In addition, oradebug provides additional details that are not
available through other tracing mechanisms.
The following steps are required to trace a user session with oradebug:
1. Obtain the SPID from v$process.
SQL> select username, spid from v$process;
2. Start the debug session with the SPID of the process that needs traced.
SQL> oradebug setospid 2280
3. Select the appropriate trace level. There are four different options
when specifying a tracing level:
• Level 1 – provides “base set” tracing information. Bind variables are
displayed as variables (:b1).
• Level 4 – provides Level 1 data and the actual data values of bind
• Level 8 – provides Level 1 data and information on wait events when the
elapsed time is greater than the CPU time.
• Level 12 – combines levels 1, 4 and 8 tracing information.
A Level 12 trace contains base set, bind variable values and wait events.
The oradebug command below will enable the maximum tracing possible:
SQL> oradebug event 10046 trace name context forever, level 12
4. Turn tracing off.
SQL> oradebug event 10046 trace name context off
5. Obtain the trace file name. The oradebug facility provides an easy way
to obtain the file name:
SQL> oradebug tracefile_name
6. Format the trace file with tkprof (as described in the earlier section
The result will be a trace file that contains more trace file information.
Viewing wait events and bind variable values can be critical to diagnosing
For more details on Oracle
utilities, see the book "Advanced
Oracle Utilities" by Bert Scalzo, Donald
K. Burleson, and Steve Callan.
You can buy it direct from the
publisher for 30% off directly from
Download your Oracle scripts now:
definitive Oracle Script collection for every Oracle professional DBA