关于v$sql_bind_capture 的问题

---先清空shared_pool
SQL> alter system flush shared_pool;

System altered.

SQL> col value_STRING format a30
SQL> desc  v$sql_bind_capture
 Name        Null?    Type
 ----------------------------------------- -------- ----------------------------
 ADDRESS         RAW(4)
 HASH_VALUE         NUMBER
 SQL_ID          VARCHAR2(13)
 CHILD_ADDRESS         RAW(4)
 CHILD_NUMBER         NUMBER
 NAME          VARCHAR2(30)
 POSITION         NUMBER
 DUP_POSITION         NUMBER
 DATATYPE         NUMBER
 DATATYPE_STRING        VARCHAR2(15)
 CHARACTER_SID         NUMBER
 PRECISION         NUMBER
 SCALE          NUMBER
 MAX_LENGTH         NUMBER
 WAS_CAPTURED         VARCHAR2(3)
 LAST_CAPTURED         DATE
 VALUE_STRING         VARCHAR2(4000)
 VALUE_ANYDATA         SYS.ANYDATA

为了方便测试 从之前测试中得到了select * from test where owner=:n order by 1,2,3,4,5,6语句对应的sql_id为0g3vjcu3d6510
SQL> select a.sql_id, a.name, to_char(a.last_captured,'yyyy-mm-dd hh24:mi:ss'), a.value_string
  from v$sql_bind_capture a
 where sql_id = '0g3vjcu3d6510'  2    3  ;

no rows selected

 

SQL> /

SQL_ID       NAME     TO_CHAR(A.LAST_CAPT VALUE_STRING
------------- -------------------- ------------------- ------------------------------
0g3vjcu3d6510 :N     2000-11-19 01:14:07 SCOTT

SQL> ! date
Sun Nov 19 01:14:22 CST 2000

SQL> ! date
Sun Nov 19 01:13:17 CST 2000

先给变量赋值为SCOTT
---------variable 定义sqlplus变量
SQL> variable n varchar2(10)

SQL>  execute :n  := 'SCOTT';

PL/SQL procedure successfully completed.

select * from test where owner=:n order by 1,2,3,4,5,6;
SQL> /

SQL_ID       NAME     TO_CHAR(A.LAST_CAPT VALUE_STRING
------------- -------------------- ------------------- ------------------------------
0g3vjcu3d6510 :N     2000-11-19 01:14:07 SCOTT

SQL> ! date
Sun Nov 19 01:14:22 CST 2000

再给变量赋值为SYS
SQL> variable n varchar2(10)

SQL>  execute :n  := 'SYS';

PL/SQL procedure successfully completed.

select * from test where owner=:n order by 1,2,3,4,5,6;

SQL> ! date
Sun Nov 19 01:15:32 CST 2000

SQL> /

SQL_ID       NAME     TO_CHAR(A.LAST_CAPT VALUE_STRING
------------- -------------------- ------------------- ------------------------------
0g3vjcu3d6510 :N     2000-11-19 01:14:07 SCOTT

可以看到绑定变量分值并没有变化

查看资料后发现:
还有个视图v$sql_bind_capture，查看是当前的捕获。捕获的间隔有一个隐含参数控制。默认是900秒，才会重新开始捕获。

说明这个视图不是实时刷新的

SQL>SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
  2    FROM SYS.x$ksppi x, SYS.x$ksppcv y
  3   WHERE x.inst_id = USERENV ('Instance')
  4     AND y.inst_id = USERENV ('Instance')
  5     AND x.indx = y.indx
  6     AND x.ksppinm LIKE '%&par%'
  7  /
Enter value for par: bind_ca
old   6:    AND x.ksppinm LIKE '%&par%'
new   6:    AND x.ksppinm LIKE '%bind_ca%'
NAME                           VALUE                DESCRIB
------------------------------ -------------------- ------------------------------------------------------------
_cursor_bind_capture_area_size 400                  maximum size of the cursor bind capture area
_cursor_bind_capture_interval  900                  interval (in seconds) between two bind capture for a cursor

SQL_ID       NAME     TO_CHAR(A.LAST_CAPT VALUE_STRING
------------- -------------------- ------------------- ------------------------------
0g3vjcu3d6510 :N     2000-11-19 01:33:02 PUBLIC