Turn on Oracle Trace At The Session Level

# Yes there are lots of ways to turn on trace an the session level – here is the one I use the most
# to get my current SID, SERIAL

SQL> select sid, serial# from v$session where audsid = userenv('SESSIONID');

# Now give my session a prefix I can find

SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER='MyTrace';
SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>999, serial#=>9999, sql_trace=>TRUE);
# - your SQL statement goes here
SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>999, serial#=>9999, sql_trace=>FALSE);

# for 10g and prior look in user_dump_dest

SQL> show parameter user_dump_dest

# or 11g and forward

SQL> SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Diag Trace';
SQL> SELECT VALUE FROM V$DIAG_INFO;

# Another favorite way to trace

SQL> ORADEBUG SETMYPID; -- current session.
SQL> ORADEBUG SETOSPID 9999; -- session with the specified OS process.
SQL> ORADEBUG SETORAPID 999999; -- session with the specified Oracle process ID.
SQL> ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12;
SQL> ORADEBUG TRACEFILE_NAME; -- Display the current trace file.
SQL> ORADEBUG EVENT 10046 TRACE NAME CONTEXT OFF;

For additional LonzoDB recent Oracle related posts.