oracle 如何获取在 ODP.NET 中通过 OracleParameter 传递的绑定变量的跟踪信息？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18928230/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get tracing info for binding variables passed through OracleParameter in ODP.NET?
提问by Leniel Maccaferri
After Googling a lot and not finding what I'm looking for I decided to ask this question.
在谷歌搜索了很多并没有找到我要找的东西之后，我决定问这个问题。
I'm using binding variablesas demonstrated in this awesome article from 2005titled The Values That Bindby Mark A. Williams, like this:
我正在使用绑定变量，如2005 年由 Mark A. Williams撰写的题为The Values That Bind的这篇很棒的文章所示，如下所示：
OracleParameter p_APP_NAME =
new OracleParameter("p_APP_NAME", OracleDbType.NVarchar2, ParameterDirection.Input);
p_APP_NAME.Size = 50;
p_APP_NAME.Value = log.Application.Name;
cmd.Parameters.Add(p_APP_NAME);
I successfully enabled ODP.NETdebug tracingbut one key info that's missing is that the logged SQL statementdoesn't show me what was the value bound to the binding variable.
我成功启用了ODP.NET调试跟踪，但缺少的一个关键信息是记录的SQL statement没有显示绑定到绑定变量的值是什么。
It's logging OracleCommand.CommandTextbut without OracleCommand.Parametersvalues. It's showing me this:
它正在记录OracleCommand.CommandText但没有OracleCommand.Parameters值。它向我展示了这个：
TIME:2013/09/20-22:59:21:890 TID:20fc OpsSqlPrepare2(): SQL: UPDATE PS_LOG SET
APP_NAME = :p_APP_NAME,
WHERE LOG_ID = :p_LOG_ID
What I'd really like to see are the actual values that were used in the query sent to the ORACLE server like this:
我真正想看到的是发送到 ORACLE 服务器的查询中使用的实际值，如下所示：
TIME:2013/09/20-22:59:21:890 TID:20fc OpsSqlPrepare2(): SQL: UPDATE PS_LOG SET
APP_NAME = 'App Name',
WHERE LOG_ID = 777
Am I missing some configuration or this info I want is not available when using ODP.NETtracing capability?
使用ODP.NET跟踪功能时，我是否缺少某些配置或我想要的此信息不可用？
If this is not built-in I guess I'll have to implement my own substitution method and log the SQL Statementmyself.
如果这不是内置的，我想我将不得不实现自己的替换方法并SQL Statement自己记录。
回答by Nick Krasnov
As one of the options, you could turn on sql tracing with bind variables dumping by setting 10046 event of level 12 or 4 for a session, either manually or automatically(in a logon trigger for a user for instance):
作为选项之一，您可以通过手动或自动（例如在用户的登录触发器中）为会话设置级别 12 或 4 的 10046 事件来打开带有绑定变量转储的 sql 跟踪：
alter session set events '10046 trace name context forever, level 12';
After that trace file will be generated in a directory specified by user_dump_destparameter.
之后将在user_dump_dest参数指定的目录中生成跟踪文件。
SQL> show parameter user_dump_dest;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string D:\...\diag\rdbms\cdb\cdb
\trace
Here is an example:
下面是一个例子：
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 ;
Among other information presented in a newly generated trace file we could find information about the query, bind variable(s) and its/their values:
在新生成的跟踪文件中提供的其他信息中，我们可以找到有关查询、绑定变量及其值的信息：
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<-- our query
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<-- value of the bind variable
游标中的解析#375980232 len=40 dep=0 uid=103
oct=3 Lid=103 tim=2640550035 hv=1641534478
ad='7ff5bd0baf0' sqlid='ap9rzz5hx1
from our query <-7
STMT
解析结束#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：
绑定#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
kxe16pln =22 avl=05 flg=05
value=1234567<-- 绑定变量的值
You could also, starting from Oracle 10g and up, query v$sql_bind_capturedynamic performance view to get information about bind variable(s) and their values:
从 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
Result:
结果：
PARSING_USER_ID SQL_FULLTEXT NAME VALUE_STRING
------------------------------------------------------------------------
103 select 1 from dual where 1 =:var :VAR 1234567
回答by Leniel Maccaferri
I agree with everything posted by Nicholas but would add that beginning with Oracle 10.1 the DBMS_MONITOR PL/SQL Packageis the preferred method of enabling/disabling tracing. Though setting the event still works as Nicholas has nicely demonstrated.
我同意 Nicholas 发布的所有内容，但要补充一点，从 Oracle 10.1 开始，DBMS_MONITOR PL/SQL 包是启用/禁用跟踪的首选方法。尽管设置事件仍然有效，尼古拉斯已经很好地证明了这一点。
As you have observed ODP does not emit bind variable values into its trace file as a security consideration.
正如您所观察到的，出于安全考虑，ODP 不会将绑定变量值发送到其跟踪文件中。
