How to See the Oracle 10g 11g Bind Variable Values Using v$sql_bind_capture

Here is the simple query to see the bind variables from Oracle database.

SELECT     s.sql_id,   s.sql_text SQLTEXT, 
bc.name BIND_VAR_NAME, 
bc.value_string BIND_VAR_STRING
FROM       v$sql s  , v$sql_bind_capture bc
WHERE      s.sql_id = bc.sql_id
AND        bc.value_string IS NOT NULL

Example

SQL> variable owner_name varchar2(15);
SQL> exec :owner_name:='SCOTT';

PL/SQL procedure successfully completed.
SQL> SELECT DISTINCT OBJECT_NAME FROM object_list WHERE OWNER=:owner_name;

SQL> SELECT  s.sql_id,   s.sql_text SQLTEXT,  bc.name BIND_VAR_NAME,  bc.value_string BIND_VAR_STRING
FROM   v$sql s  , v$sql_bind_capture bc
where  s.sql_id = bc.sql_id
and  bc.value_string IS NOT NULL
and  lower(s.sql_text) like '%object_list%'

SQL_ID
-------------
SQLTEXT
---------------------------------------------
BIND_VAR_NAME
---------------------------------------------
BIND_VAR_STRING
---------------------------------------------
bky7kjtg041ca
SELECT DISTINCT OBJECT_NAME FROM object_list WHERE OWNER=:owner_name
:OWNER_NAME
SCOTT
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google