Whenever there is an error condition that do not provide enough information to immediately diagnose the root cause and find a solution, tracing the session that is generating the problem is the best first option to get more information.
In many cases I did find on the raw trace the additional information required to get to the root cause of a problem.
This method is good also to generate high level SQL traces to be used for SQL tuning.
This is a short reference for running oradebug with event 10046 that enable SQL statement tracing including binds and waits.
To trace same session:
oradebug setmypid ;
To trace other session first you need to get it's spid:
select a.username,b.spid
from v$session a, v$process b
where a.username like '%&user%'
and a.paddr=b.addr ;
Then from your session, connected as sys, attach to the other session:
oradebug setospid &ospid ;
Then check the tracefile name:
oradebug tracefile_name ;
At this moment you can start tracing, there are many different events that can be used for different purposes, event 10046 is very useful for debugging, level 12 provide information about what are the waits the session is waiting for and also the values of the bind variables :
oradebug event 10046 trace name context forever, level 12 ;
Check the trace as it run:
! tail
To interrupt the trace you can either exit the session or execute this command:
oradebug event 10046 trace name context off ;
More information about oradebug and its many options can be find on this page "Utiliwiki"
