Queries & bind variable values
From: sumit Tyagi <dba.tyagisumit_at_gmail.com>
Date: Mon, 29 Jun 2015 14:53:37 +0530
Message-ID: <CAFW4NVzU5W3Z6uNJ7ho76Rq=7CQVe=5ZMUy0TfX_eDwEWVQ7Xw_at_mail.gmail.com>
Hi Oracle-L team ,
FROM GV$LOCK L1, GV$SESSION S1, GV$LOCK L2, GV$SESSION S2 WHERE S1.SID=L1.SID AND S2.SID=L2.SID
Date: Mon, 29 Jun 2015 14:53:37 +0530
Message-ID: <CAFW4NVzU5W3Z6uNJ7ho76Rq=7CQVe=5ZMUy0TfX_eDwEWVQ7Xw_at_mail.gmail.com>
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;
--Received on Mon Jun 29 2015 - 11:23:37 CEST
*--*
*BR*
*Sumit Tyagi*
*+91-7829543355*
-- http://www.freelists.org/webpage/oracle-l