Performance Tuning

Trace for a specific error

User Rating:

/ 1

Details: 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: 4914

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.