Tracing Commands

====================================
Trace Specific ORA-XXXXX - Current Session
====================================

Activate:

SQL> alter session set events '3113 trace name errorstack level 3';

 

Stop:
SQL> alter session set events '3113 trace name context off'

 

====================================
Trace Specific ORA-XXXXX – for all system
====================================

Activate:

SQL> alter system set events '3113 trace name errorstack level 3';

 

Stop:
SQL> alter system set events '3113 trace name context off'

 

Or at init.ora

 

event="3113 trace name ERRORSTACK level 3"

 

You'll need to bounce the database.
 

=============================
10046 Trace - Current Session
=============================
Open new session is SQL*Plus
ALTER SESSION SET max_dump_file_size = unlimited;
ALTER SESSION SET tracefile_identifier = '10046';
ALTER SESSION SET statistics_level = ALL;
ALTER SESSION SET events '10046 trace name context forever, level 12';
<Your Query>
ALTER SYSTEM SET EVENTS '10046 trace name context off';
EXIT

 

The file generated in user_dump_dest.

 

Reference:
Note 376442.1 - Recommended Method for Obtaining 10046 trace for Tuning

you can use TRANLZR to examine the trace file

 

===========================
10046 Trace - Other Session
===========================
Find the OS process id that runs the query

 

connect / as sysdba
oradebug setospid <os process id>;
oradebug unlimit;
oradebug event 10046 trace name context forever,level 12;
<wait 5 minutes>
oradebug event 10046 trace name context off;
exit

 

The file generated in user_dump_dest.

 

Reference:
Note 376442.1 - Recommended Method for Obtaining 10046 trace for Tuning