Re: Queries & bind variable values

From: <l.flatz_at_bluewin.ch>
Date: Mon, 29 Jun 2015 09:29:48 +0000
Message-ID: <21291468.18004.1435570188130.JavaMail.webmail_at_bluewin.ch>

Hi,
You can use dbms_xplan.display_cursor (e.g. format 'ADVANCED') or simply query gv$sql_bind_capture. Thanks
Lothar
----Ursprüngliche Nachricht----

Von : dba.tyagisumit_at_gmail.com
Datum : 29/06/2015 - 11:23 (GMT)
An : oracle-l_at_freelists.org
Betreff : Queries & bind variable values Hi Oracle-L team ,
How can we get the information ( report) regarding the actual values that were used at the time of query execution . We have sql_id info . Use case : client want to know the values of bind variables for the sessions that are captured in blocking session query . Below is the blocking session query .

SELECT DISTINCT S1.USERNAME || '_at_' || S1.MACHINE
|| ' ( INST=' || S1.INST_ID || ' SID=' || S1.SID || ' ) IS BLOCKING '
|| S2.USERNAME || '_at_' || S2.MACHINE || ' ( INST=' || S1.INST_ID || ' SID='

|| S2.SID || ' ) ' AS BLOCKING_STATUS
FROM GV$LOCK L1, GV$SESSION S1, GV$LOCK L2, GV$SESSION S2 WHERE S1.SID=L1.SID AND S2.SID=L2.SID

AND S1.INST_ID=L1.INST_ID AND S2.INST_ID=L2.INST_ID
AND L1.BLOCK > 0 AND L2.REQUEST > 0
AND L1.ID1 = L2.ID1 AND L1.ID2 = L2.ID2;

--
--

BR
Sumit Tyagi
+91-7829543355

--

http://www.freelists.org/webpage/oracle-l Received on Mon Jun 29 2015 - 11:29:48 CEST