26 | 06 | 2017
Latest Articles
Popular Articles

Performance Tuning

Trace for a specific error

User Rating:  / 1
PoorBest 

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.