Solved

Oracle 10053 Trace

Posted on 2011-03-16
3
819 Views
Last Modified: 2012-05-11
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.

0
Comment
Question by:sanpradeep
  • 2
3 Comments
 

Author Comment

by:sanpradeep
ID: 35153797
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
 
LVL 7

Accepted Solution

by:
MrNed earned 500 total points
ID: 35153845
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
 

Author Comment

by:sanpradeep
ID: 35153913
Thanks for the reply... It helped me..
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

680 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question