Look at dba_hist_bind. If you know when the query was executed, you can
narrow it down by SNAP_ID
select SNAP_ID, NAME, VALUE_STRING, SQL_ID
from DBA_HIST_SQLBIND
where sql_id in ('xxxxxxxxxx');
On Mon, Jun 29, 2015 at 4:29 AM, l.flatz@xxxxxxxxxx <l.flatz@xxxxxxxxxx>
wrote:
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@xxxxxxxxx
Datum : 29/06/2015 - 11:23 (GMT)
An : oracle-l@xxxxxxxxxxxxx
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 || '@' || S1.MACHINE
|| ' ( INST=' || S1.INST_ID || ' SID=' || S1.SID || ' ) IS BLOCKING '
|| S2.USERNAME || '@' || 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 <%2B91-7829543355>*
