How to find value of bind variables used by SQLs

There is one good useful feature in Oracle 10g to catch the value of bind variables without a trace.

This feature is the view V$SQL_BIND_CAPTURE. This view gives you information about the values binded to a specific sql_id with the capture time info. When we use this view with V$SESSION we can capture the values that are binded to the working sqls. This view is populated only when you set STATISTICS_LEVEL parameter to ALL/TYPICAL . Bind capturing is disabled when the STATISTICS_LEVEL initialization parameter is set to BASIC.

Here is an example of the usage;
col sid format a4
col username format a5
col sql_hash_value format 99999999
col sqlid format a14
col sql_child_number format 9
col name format a4
col value_string format a8
col last_captured format a9

select s.sid,
s.username,
–sq.sql_text,
s.sql_hash_value,
s.sql_id,
s.sql_child_number,
spc.name,
spc.value_string,
last_captured
from v$sql_bind_capture spc, v$session s,v$sql sq
where s.sql_hash_value = spc.hash_value
and s.sql_address = spc.address
and sq.sql_id=s.sql_id
and spc.was_captured=’YES’
and s.type<>’BACKGROUND’
and s.status=’ACTIVE’;

SID USERN SQL_HASH_VALUE SQL_ID SQL_CHILD_NUMBER NAME VALUE_ST LAST_CAPT
———- —– ————– ————- —————- —- ——– ———
########## OKUR ######### 4s6c5w6zr0dub 0 :1 20070301 02-MAR-07
########## OKUR ######### 4s6c5w6zr0dub 0 :2 20070301 02-MAR-07
########## OKUR ######### 4s6c5w6zr0dub 0 :3 NULL 02-MAR-07
########## OKUR ######### 4s6c5w6zr0dub 0 :4 NULL 02-MAR-07
########## OKUR ######### 4s6c5w6zr0dub 0 :5 NULL 02-MAR-07
########## OKUR ######### 4s6c5w6zr0dub 0 :6 NULL 02-MAR-07
########## OKUR ######### 4s6c5w6zr0dub 0 :7 NULL 02-MAR-07

resources used:

Watching SQL Execute on Oracle – Part II James Koopmann

Oracle Documentation for 10g R1

About these ads