How to find the bind variable value of a SQL that is currently executing

There are two reliable ways to get the bind variable values of a SQL that is currently executing.

1. SQL Monitoring (V$SQL_MONITOR.BIND_XML)

2. Errorstack Trace

We look at both of these methods in details. 

I've not included 10046 ( level 8 or 12 ) trace here as it may not report the bind variable value if you have enabled the trace after the SQL has crossed the parse phase.

SQL Monitoring (V$SQL_MONITOR.BIND_XML)

SQL Monitoring was introduced in 11.1 but the column BIND_XML was added only in 11.2

We run a query with bind variables that takes little longer to complete.

SQL> VAR BIND1 NUMBER;

SQL> EXEC :BIND1:=999;

SQL> select count(*) from DBA_OBJECTS A,DBA_OBJECTS B WHERE A.OBJECT_ID>:BIND1;

While this SQL is still running, query v$sql_monitor from a different session.

SQL> select xmltype(binds_xml) from v$sql_monitor where sid =74 and status = 'EXECUTING';

XMLTYPE(BINDS_XML)

------------------

<binds>

<bind name=":BIND1" pos="1" dty="2" dtystr="NUMBER" maxlen="22" len="3">999</bind>

</binds>

Note that SQL Monitoring kicks in for SQL statements that run for 5 seconds or more.

If you have a bind variable in the SELECT clause of the query, bind_xml is not populated. For e.g.

select :bind1 AS BIND, ENAME from SCOTT.EMP ;

 ERRORSTACK

Errorstack trace has lot of information in it. Here I focus on the bind variable section of the trace file alone.

This is how you can take an errorstack trace. 12280 is the OS Process ID of the session that runs the SELECT.

connect / as sysdba

oradebug setospid 12280

oradebug unlimit

oradebug dump errorstack 3

oradebug tracefile_name /* This tells you the file name  */

exit

Header section of the trace file shows you the the Current SQL statement text and its SQL ID.

----- Error Stack Dump -----

----- Current SQL Statement for this session (sql_id=9z7qgrmf5at7b) 

select count(*) from DBA_OBJECTS A,DBA_OBJECTS B WHERE A.OBJECT_ID>:BIND1

Now search the trace file with the keyword "Dump Cursor sql_id=9z7qgrmf5at7b". Scroll down a bit and you would see the bind information.

----- Bind Info (kkscoacd) -----

Bind#0

oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

oacflg=03 fl2=1000000 frm=00 csi=00 siz=24 off=0

kxsbbbfp=2b479dde1cc8 bln=22 avl=03 flg=05

value=999

oacdty is the data type of the variable. 02 indicates a NUMBER datatype. These are some of other identifiers.

01 - Char

02 - Number

08 - Long

09 - Varchar

12 - Date

112- CLOB

113- BLOB

114- BFILE