determining bind values in deadlock situations

I'm trying to find an efficient way to get the bind values for statements that are involved in a deadlock situation.

So far, I'm able to get the bind values for statements that are part of the session that encounters the ora-60 by setting event='name errorstack level 2, forever'.

The problem is forcing an errorstack on the other session that is involved in the deadlock, the one that does not get the ora-60.

I have come with several possibilities, but none of them seem to be even efficient or provide the correct bind values:

1. Write some code that parses the lmd0 trace, finds the session of interest and dump an errorstack. Problem: requires writing a lmd0 parser.
2. Enable sql level auditing on the tables of interest. Problem: captures huge number of statements and that could cause other issues (ie storage, performance overhead,...).
3. Enable 10046 trace database wide Problem: same as #2
4. AFTER SERVERERROR trigger that queries v$sql_bind_capture on when ora 60 is raised Problem: bind values in v$sql_bind_capture are captured only every 15 minutes (default setting for _cursor_bind_capture_interval)
5. dump errorstack for all sessions Problem: Don't know how to do that except for looping through gv$session and dumping error stacks one session at a time...

I have opened an SR, but the analyst is no help, he didn't even know that errorstack level 2 contains bind values...

Regards,

Vlado Barun, M.Sc.
Sr. Manager, Database Engineering and Operations Jewelry Television
Mobile: 865 335 7652
Email: vlado.barun_at_jtv.com<mailto:vlado.barun_at_jtv.com>

--
http://www.freelists.org/webpage/oracle-l