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.