通过查询视图 V$SQL_BIND_CAPTURE 可以获得执行过的sql的绑定变量的值.没有的话 要么没采集要么已经被out出对应的shared cursor
已经没在shared pool里了.这时候可以去dba_hist_sqlstat 或者 dba_hist_sqlbind 里查找.
如果初始化参数STATISTICS_LEVEL设置为BASIC,则会禁止采集绑定变量
SYS@mydb1>show parameter statistics_level
NAME TYPE VALUE
------------------------------------ --------------- ------------------------------
statistics_level string TYPICAL
SYS@mydb1>
SQL> declare
2 eno NUMBER(4);
3 begin
4 eno := 7369;
5 execute immediate 'select * from emp where empno= :eno' using eno;
6 end;
7 /
PL/SQL procedure successfully completed
SQL> SQL> select s.SQL_ID,s.VERSION_COUNT,s.SQL_TEXT from v$sqlarea s where s.SQL_TEXT like '%select * from emp where empno= :eno%';
SQL_ID VERSION_COUNT SQL_TEXT
------------- ------------- --------------------------------------------------------------------------------
gnbphfmc7dvj2 1 select * from emp where empno= :eno
50zq43ruxfwf1 1 select s.SQL_ID,s.VERSION_COUNT,s.SQL_TEXT from v$sqlarea s where s.SQL_TEXT li
bshtxf1tzmzhd 1 declare eno NUMBER(4); begin eno := 7369; execute immediate 'select * from e
SQL>
SQL> select b.SQL_ID,b.NAME,b.POSITION,b.DATATYPE_STRING,b.LAST_CAPTURED,b.VALUE_STRING from v$sql_bind_capture b where b.SQL_ID='gnbphfmc7dvj2';
SQL_ID NAME POSITION DATATYPE_STRING LAST_CAPTURED VALUE_STRING
------------- --------------- --------- --------------- ------------------- --------------
gnbphfmc7dvj2 :ENO 1 NUMBER 2014-11-21 13 7369
SQL>