作为其中一个选项,您可以通过为会话设置级别12或4的10046事件来手动或自动打开带有绑定变量转储的sql跟踪(例如,在用户的登录触发器中):
alter session set events '10046 trace name context forever, level 12';
之后,将在user_dump_dest参数指定的目录中生成跟踪文件.
SQL> show parameter user_dump_dest;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string D:\...\diag\rdbms\cdb\cdb
\trace
这是一个例子:
SQL> alter session set events '10046 trace name context forever, level 12';
2 variable var number;
3 exec :var := 1234567; -- our bind variable
4 select 1 from dual where 1234567 = :var ;
在新生成的跟踪文件中提供的其他信息中,我们可以找到有关查询,绑定变量及其值的信息:
PARSING IN CURSOR #375980232 len=40 dep=0 uid=103
oct=3 lid=103 tim=2640550035 hv=1641534478
ad=’7ff5bd0baf0′ sqlid=’ap9rzz5hxgp0f’
select 1 from dual where 1234567 = :var
END OF STMT
PARSE #375980232:c=0,e=375,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=2640550034
BINDS #375980232:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=01 csi=178 siz=24 off=0
kxsbbbfp=16646e10 bln=22 avl=05 flg=05
value=1234567
您也可以从Oracle 10g开始查询v $sql_bind_capture动态性能视图,以获取有关绑定变量及其值的信息:
select t.parsing_user_id
, t.sql_fulltext -- text of a query
, bc.name -- name of a bind variable
, bc.value_string -- value of a bind variable
from v$sqlarea t
join v$sql_bind_capture bc
on (bc.sql_id = t.sql_id)
join v$session s
on (s.user# = t.parsing_schema_id)
where s.username = user
and bc.name in (':VAR') -- name of a bind variable(s),
-- value(s) of which we want to know
结果:
PARSING_USER_ID SQL_FULLTEXT NAME VALUE_STRING
------------------------------------------------------------------------
103 select 1 from dual where 1 =:var :VAR 1234567