v$sql_bind_capture 获取绑定变量

exec :v_id := 509105861;
exec :v_date := '2009-12-26 12:37:51';

select * from tmp_xf_test where id =:v_id and gmt_create > to_date(:v_date ,'yyyy-mm-dd hh24:mi:ss');
---

1  SELECT hash_value,sql_id,NAME,POSITION,DATATYPE_STRING,LAST_CAPTURED,value_string FROM v$sql_bind_capture
2  WHERE hash_value = 615294788
3*
SQL> /

HASH_VALUE SQL_ID        NAME                   POSITION DATATYPE_STRING      LAST_CAPTURED       VALUE_STRING
---------- ------------- -------------------- ---------- -------------------- ------------------- ------------------------------
615294788 8n3ruvckat9u4 :V_ID                         1 NUMBER               2009-12-29 10:54:38 509105861
615294788 8n3ruvckat9u4 :V_DATE                       2 VARCHAR2(128)        2009-12-29 10:54:38 2009-12-26 12:37:51

exec :v_id := 509197545;
exec :v_date := '2009-12-25 00:00:00';

select * from tmp_xf_test where id =:v_id and gmt_create > to_date(:v_date ,'yyyy-mm-dd hh24:mi:ss');
--------
1  SELECT hash_value,sql_id,NAME,POSITION,DATATYPE_STRING,LAST_CAPTURED,value_string FROM v$sql_bind_capture
2  WHERE hash_value = 615294788
3*
SQL> /

HASH_VALUE SQL_ID        NAME                   POSITION DATATYPE_STRING      LAST_CAPTURED       VALUE_STRING
---------- ------------- -------------------- ---------- -------------------- ------------------- ------------------------------
615294788 8n3ruvckat9u4 :V_ID                         1 NUMBER               2009-12-29 11:23:46 509197545
615294788 8n3ruvckat9u4 :V_DATE                       2 VARCHAR2(128)        2009-12-29 11:23:46 2009-12-25 00:00:00
---视图中的绑定变量值发生改变了。

这个值的变换频率_cursor_bind_capture_interval 隐含参数控制

21:16:19 sys@ORCL> col name for a55
21:16:20 sys@ORCL> col value for a45
21:16:20 sys@ORCL> set lines 200
21:16:20 sys@ORCL> set pages 200
21:16:20 sys@ORCL>
21:16:20 sys@ORCL> select /* tbsql */ /*+ rule */
21:16:20   2  x.ksppinm  name,
21:16:20   3  y.ksppstvl  value,
21:16:20   4  y.ksppstdf  isdefault,
21:16:20   5  decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE')ismod,
21:16:20   6  decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE')  isadj
21:16:20   7  from
21:16:20   8  sys.x$ksppi x,
21:16:20   9  sys.x$ksppcv y
21:16:20  10  where
21:16:20  11  x.inst_id = userenv('Instance') and
21:16:20  12  y.inst_id = userenv('Instance') and
21:16:20  13  x.indx = y.indx and
21:16:20  14  x.ksppinm like '%&PAR%'
21:16:20  15  order by
21:16:20  16  translate(x.ksppinm, ' _', ' ');
输入 par 的值:  _cursor_bind_capture_interval
原值   14: x.ksppinm like '%&PAR%'
新值   14: x.ksppinm like '%_cursor_bind_capture_interval%'

NAME                                                    VALUE
ISDEFAULT ISMOD      ISADJ
------------------------------------------------------- ------------------------
--------------------- --------- ---------- -----
_cursor_bind_capture_interval                            900
TRUE      SYSTEM_MOD FALSE

默认值900取一次绑定值
可以通过
alter system set "_cursor_bind_capture_interval"=60;
来修改，这个只在特殊情况下用于排查问题偶然用到，没事悠着点，别乱改 丷

关于v$sql_bind_capture 视图请看文档：
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2114.htm#REFRN30310