James's Blog

Oracle: trace event

init.ora: event='1401 trace name errorstack, level 12'

user session: alter session set events '10046 trace name context forever, level 4';

* 10046 trace name context forever, level 4

Trace SQL statements and show bind variables in trace output.

* 10046 trace name context forever, level 8

This shows wait events in the SQL trace files

* 10046 trace name context forever, level 12

This shows both bind variable names and wait events in the SQL trace files

* 1401 trace name errorstack, level 12
1401 trace name errorstack, level 4
1401 trace name processstate

Dumps out trace information if an ORA-1401 "inserted value too large for column" error
occurs. The 1401 can be replaced by any other Oracle Server error code that you want
to trace.

* 60 trace name errorstack level 10

Show where in the code Oracle gets a deadlock (ORA-60), and may help to diagnose
the problem.



============================================================
SQL> oradebug setmypid
SQL> oradebug unlimit
SQL> oradebug dump systemstate 10
SQL> oradebug tracefile_name
or to trace a specific error
SQL> select username, spid from v$process;
SQL> oradebug setospid 2280
SQL> oradebug event 10046 trace name context off

posted by James Pei @ 11:33 AM