Tracing Oracle Sessions

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:

Please share this post Share on Facebook1Share on Google+0Share on LinkedIn3Share on Reddit0Tweet about this on Twitter

Gokhan Atil is a database architect who has hands-on experience with both RDBMS and noSQL databases (Oracle, PostgreSQL, Microsoft SQL Server, Sybase IQ, MySQL, Cassandra, MongoDB and ElasticSearch), and strong background on software development. He is certified as Oracle Certified Professional (OCP) and is awarded as Oracle ACE (in 2011) and Oracle ACE Director (in 2016) for his continuous contributions to the Oracle users community.


  1. gram

    You can issue the following command to disable oradebug tracing:


  2. Pingback: Watch out for optimizer_adaptive_features as It may have a huge negative impact | bdt's oracle blog

  3. Preguntón Cojonero Cabróncete

    I use PL SQL Developer to connect to ORACLE Server (Solaris).

    How can I use dDBMS_OUTPUT.PUT_LINE to log a file

    “alter session set events ‘10046 trace name context forever, level 4′”;

    to enable extended sql tracing. and to create trace file in user_dump_dest directory ?

    what is mena 10046 value ? and level 4 ?

    • Gokhan Atil

      Hi Preguntón,

      You need to use UTL_FILE instead of DBMS_OUTPUT package to write logs to files.

      10046 is a special EVENT code for Oracle. When you signal it, Oracle performs sql trace.

      1: Enable standard SQL_TRACE functionality
      4: As Level 1 PLUS trace bind values
      8: As Level 1 PLUS trace waits
      16: Generate STAT line dumps for each execution
      32: Never dump execution statistics
      64: Adaptive dump of STAT lines

      I usually use level 12 (4+8), so the trace file contains standard SQL_TRACE output, binds, waits and default STAT line tracing.

Leave Comment

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