Oracle – How to Trace Binding Variables from UPDATE Statement

oracle

Is there a way to trace binding variables from UPDATE statement? I tried to look into v$sql_bind_capture but according to documentation this only works for WHERE or HAVING statements.

Best Answer

Set up environment:

SQL> create table t1 (c1 number, c2 number);

Table created.

SQL> insert into t1 values(1, 1);

1 row created.

SQL> commit;

Commit complete.

SQL> variable B1 number
SQL> variable B2 number

SQL> exec :B1 := 1;

PL/SQL procedure successfully completed.

SQL> exec :B2 := 2;

PL/SQL procedure successfully completed.

SQL> update t1 set c2 = :b2 where c1 = :b1;

1 row updated.

SQL> rollback;

Rollback complete.

Find my SQL and the captured bind values:

SQL> select prev_sql_id from v$session where sid = sys_context('userenv', 'sid');

PREV_SQL_ID
-------------
94abc43hj1btv

SQL> select sql_text from v$sql where sql_id = '94abc43hj1btv';

SQL_TEXT
--------------------------------------------------------------------------------
update t1 set c2 = :b2 where c1 = :b1

SQL> select name, position, datatype_string, value_string, was_captured
  2  from v$sql_bind_capture where sql_id = '94abc43hj1btv';

NAME         POSITION DATATYPE_STRING      VALUE_STRING         WAS
---------- ---------- -------------------- -------------------- ---
:B2                 1 NUMBER                                    NO
:B1                 2 NUMBER               1                    YES

Problem: the value of :B2 was not captured because it is not part of a WHERE/HAVING clause.

Enable SQL tracing in my session for this specific SQL statement and find the trace file:

SQL> alter session set events 'sql_trace[sql: 94abc43hj1btv]  level=12';

System altered.

SQL> select TRACEFILE from v$process where addr = (select paddr from v$session where sid = sys_context('userenv', 'sid'));

TRACEFILE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_2231.trc

Run the SQL again:

SQL> update t1 set c2 = :b2 where c1 = :b1;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from t1;

        C1         C2
---------- ----------
         1          2

And the content of the trace file:

PARSING IN CURSOR #140116042834336 len=37 dep=0 uid=85 oct=6 lid=85 tim=437775551 hv=3775967035 ad='63073648' sqlid='94abc43hj1btv'
update t1 set c2 = :b2 where c1 = :b1
END OF STMT
BINDS #140116042834336:

 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=00 csi=00 siz=48 off=0
  kxsbbbfp=7f6f4ef446c8  bln=22  avl=02  flg=05
  value=2
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=00 csi=00 siz=0 off=24
  kxsbbbfp=7f6f4ef446e0  bln=22  avl=02  flg=01
  value=1

:B2 is the first bind variable in the SQL text, so it is labelled as Bind 0, and you can see the actual value as value=2.

You can set the above systemwide (alter system instead of alter session), but each server process will have a seperate trace file and you will need to collect the required information from them.

Related Question