Re: Queries & bind variable values

From: Karth Panchan <keyantech_at_gmail.com>
Date: Mon, 29 Jun 2015 06:25:32 -0400
Message-Id: <6F3F15B9-CC8B-4B40-B234-D3AB02E810F3_at_gmail.com>

In my experience able to get values in near real time from v$sql_monitor.binds_xml column. Data stored in XML

Karth

Sent from my IPhone

> On Jun 29, 2015, at 5:23 AM, sumit Tyagi <dba.tyagisumit_at_gmail.com> wrote:
>
> 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 - 12:25:32 CEST