Oracle 10053 Trace

I need to enable the 10053 trace for a sql_id via procedure.

Manually I can generate it as below:
alter session set events='10053 trace name context forever, level 1';
Run the sql I want to trace..
ALTER SESSION SET EVENTS '10053 trace name context off';

With the sql_id, I can get the sql text. But what about bind variables. How can I get the bind variables of the last run of the query. Is it possible?
If i get it, I can generate the sql statement and run it with the bind variables and get the trace.

sanpradeepAsked:
Who is Participating?
 
MrNedConnect With a Mentor Commented:
Try using OTHER_XML instead as per http://kerryosborne.oracle-guy.com/2009/07/creating-test-scripts-with-bind-variables/

Other option might be to flush shared pool right before they run the problematic query.
0
 
sanpradeepAuthor Commented:
I found V$SQL_BIND_CAPTURE.
But it is not refreshing this view fast..

Session 1:
 variable b1 number;
 exec :b1:=7902;
 select * from emp where empno = :b1;
SELECT NAME, VALUE_STRING, LAST_CAPTURED FROM V$SQL_BIND_CAPTURE WHERE SQL_ID = 'fr63tdr4rzhu0';
NAME       VALUE_STRING                   LAST_CAPTURED
---------- ------------------------------ --------------------
:B1        7902                           16-MAR-11

Session 2:
variable b1 number
exec :b1:=7844;
select * from emp where empno = :b1;
SELECT NAME, VALUE_STRING, LAST_CAPTURED FROM V$SQL_BIND_CAPTURE WHERE SQL_ID = 'fr63tdr4rzhu0'
/
NAME       VALUE_STRING                   LAST_CAPTURED
---------- ------------------------------ --------------------
:B1        7902                           16-MAR-11

However, after some time.. if i again query this view, it showed me 7844 value for the bind variable.
How much time do I need to wait to get this view refreshed??

0
 
sanpradeepAuthor Commented:
Thanks for the reply... It helped me..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.