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

AWS Big Data Specialist. Oracle Certified Professional (OCP) for EBS R12, Oracle 10g and 11g. Co-author of "Expert Oracle Enterprise Manager 12c" book published by Apress. Awarded as Oracle ACE (in 2011) and Oracle ACE Director (in 2016) for the continuous contributions to the Oracle users community. Founding member, and vice president of Turkish Oracle User Group (TROUG). Presented at various international conferences including Oracle Open World.


  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.

This site uses Akismet to reduce spam. Learn how your comment data is processed.