Oracle Database 12c -- How to Enable SQL Trace for a Session

Problem

You want to enable SQL Trace for a session

Solution

1) Enable SQL Trace in the current session:

To start tracing execute:

SQL> alter session set tracefile_identifier='10046';

SQL> alter session set timed_statistics = true;
SQL> alter session set statistics_level=all;
SQL> alter session set max_dump_file_size = unlimited;

SQL> alter session set events '10046 trace name context forever,level 12';

To stop tracing:

SQL> alter session set events '10046 trace name context off';

2) Enable SQL Trace for another session:

First identify the OS SPID or the Oracle process id (PID) of the session to be traced

Note that beginning from Oracle Database 12c some Oracle processes maybe combined into single process as threads, in this case additionally the thread id (STID) will be required

SQL> select s.SID, p.PID, p.SPID, p.STID
from v$process p, v$session s
where s.paddr = p.addr;

Once PID or SPID (and optionally STID) identified use oradebug to connect to the process and enable SQL Trace:

SQL> oradebug { setospid <SPID> [<STID>] | setorapid <PID> }
SQL> oradebug unlimit
SQL> oradebug event 10046 trace name context forever,level 12 

To stop tracing:

SQL> oradebug event 10046 trace name context off

3) Using a Logon trigger for a specific user/session/any other criteria:

First grant explicitly the ALTER SESSION privilege to the user:

SQL> grant alter session to <user>;

Create a logon trigger similar to:

CREATE OR REPLACE TRIGGER enable_sql_trace
  AFTER LOGON ON DATABASE
  WHEN (USER like '<user>')
  BEGIN
    EXECUTE IMMEDIATE 'alter session set tracefile_identifier=''From_Trigger''';
    EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';
    EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED';
    EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12''';
  END;