Performance Tuning

Trace for a specific error

User Rating:  / 1
Parent Category: Articles
Created on Thursday, 18 March 2010 22:20
Last Updated on Monday, 12 March 2012 13:54
Published on Thursday, 18 March 2010 22:20
Written by Guy Lambregts
Hits: 2735

Trace for a specific Oracle error


We can trace with event 10046 to capture as much as possible of a given session. We can use this if we wonder

1. why is this happening so slow ( 10046 trace at level 8 )

2. what is the sql behind the execution of a stored procedure



Sometimes I use sql trace to capture the sql causing a very specific error.  This is the sql we can use to trace for ORA 1405


SQL> alter session set events='1405 trace name context forever,level 1';
Session altered.

have your piece of code executed which is causing ORA 1405

stop the session trace with

SQL> alter session set events='1045 trace name context off';
Session altered.


In the user dump directory we should find a trace file with the sql causing the ORA 1405. I sometimes used this to nexactly find that piece of code causing the headaches.


I remember users were complaining they got the "ORA-00942: table or view does not exist" error. It was hard to find it. I decided to trace for a specific event 942.

SQL> alter system set events='942 trace name errorstack level 1';

System altered.

I asked to reproduce the error

SQL> alter system set events='942 trace name context off';

System altered.