如何獲取繫結變數值

Bind Capture History
As of 10g, Oracle captures the bind value and stores it into the repository(AWR), but with following restrictions.

• Captured periodically(_cursor_bind_capture_interval), not at real time.
• Captured under maximum size(_cursor_bind_capture_area_size)
• Only bind variables in WHERE clause are captured

Ouch! Too many restrictions, aren’t they?

Simple demonstration:

UKJA@ukja102> var bid1 number;
UKJA@ukja102> var eid1 number;
UKJA@ukja102>
UKJA@ukja102> exec :bid1 := dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

UKJA@ukja102>
UKJA@ukja102> var b1 number;
UKJA@ukja102> var b2 number;
UKJA@ukja102> var b3 varchar2(1);
UKJA@ukja102> begin
  2    :b1 := 1;
  3    :b2 := 2000;
  4    :b3 := 'y';
  5  end;
  6  /

PL/SQL procedure successfully completed.

UKJA@ukja102>
UKJA@ukja102> update t1 set c3 = :b3 where c1 between :b1 and :b2;

2000 rows updated.

UKJA@ukja102> commit;

Commit complete.

UKJA@ukja102>
UKJA@ukja102> exec :eid1 := dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

UKJA@ukja102>
UKJA@ukja102> var sql_id varchar2(100);
UKJA@ukja102> begin
  2    select sql_id into :sql_id
  3    from dba_hist_sqltext
  4    where sql_text like 'update t1 set c3 = :b3 where c1 between :b1 and :b2';
  5  end;
  6  /

PL/SQL procedure successfully completed.

UKJA@ukja102>
UKJA@ukja102> print sql_id

SQL_ID
--------------------------------------------------------------------------------
bvy5d4xupjkku                                                                   

UKJA@ukja102>
UKJA@ukja102> col name format a10
UKJA@ukja102> col position format 99
UKJA@ukja102> col value_string format a20
UKJA@ukja102> select snap_id, name, position, value_string, last_captured -
> from dba_hist_sqlbind
  2  where sql_id = :sql_id
  3  order by snap_id desc;

   SNAP_ID NAME       POSITION VALUE_STRING         LAST_CAPTURED
---------- ---------- -------- -------------------- -------------------
      6701 :B1               2 1                    2009/05/06 11:10:13
      6701 :B2               3 2000                 2009/05/06 11:10:13
      6701 :B3               1
      6700 :B2               3 1000                 2009/05/06 11:10:03
      6700 :B3               1
      6700 :B1               2 1                    2009/05/06 11:10:03
      6699 :B1               2 1                    2009/05/06 11:05:50
      6699 :B3               1
      6699 :B2               3 2000                 2009/05/06 11:05:50
      6697 :B2               3 2000                 2009/05/06 10:47:51
      6697 :B3               1
      6697 :B1               2 1                    2009/05/06 10:47:51         

12 rows selected.