sanpradeep
asked on
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the reply... It helped me..
ASKER
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??