Solved

Oracle 10053 Trace

Posted on 2011-03-16
3
809 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

929 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now