Tracing is invaluable for DBAs who want to solve performance problems. With trace data, you can follow all database calls and track the execution of a given set of SQL statements of a session. To gather trace at your own session, you can issue the following commands:

If you exit from your session or issue the following command, tracing will stop:

Trace files will be located in user_dump_dest:

If you use parallel queries then some of your trace files will be located in background_dump_dest:

Name of the trace files will be generated by Oracle. You can use tracefile identifier to be able to find your trace files easier:

So your trace files will be look like this:


You are not limited to trace your own sessions, you can trace any sessions if you have permission to execute DBMS_MONITOR package. You enable tracing with issuing the following command:

The default of waits is TRUE and the default of binds is FALSE. You can disable tracing by issuing the following command:

You can also use oradebug to trace a session. First, you need to identify the OS process ID of the session:

After the OS process id has been determined then the trace can be started:

We can use SETOSPID or SETORAPID. Please note that 29716 is a sample value. If we’ll use SETOSPID, then we should type value of p.SPID. If we’ll use SETORAPID, then we need to type the value of p.PID (i.e. 115).

You can issue the following command to disable oradebug tracing:

2 Responses to “Tracing Oracle Sessions”

  1. You can issue the following command to disable oradebug tracing:



  1. Watch out for optimizer_adaptive_features as It may have a huge negative impact | bdt's oracle blog - […] To diagnose: I dropped the PDB, plugged it back, re-launched noncdb_to_pdb.sql and enabled a 10046 trace on the session. …

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">