如何使用参数值记录/跟踪 Oracle 存储过程调用？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/250792/
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 do I log/trace Oracle stored procedure calls with parameter values?
提问by Clyde
We're looking for a way to log any call to stored procedures in Oracle, and see what parameter values were used for the call.
我们正在寻找一种方法来记录对 Oracle 中存储过程的任何调用，并查看调用使用了哪些参数值。
We're using Oracle 10.2.0.1
我们使用的是 Oracle 10.2.0.1
We can log SQL statements and see the bound variables, but when we track stored procedures we see bind variables B1, B2, etc. but no values.
我们可以记录 SQL 语句并查看绑定变量，但是当我们跟踪存储过程时，我们会看到绑定变量 B1、B2 等，但没有值。
We'd like to see the same kind of information we've seen in MS SQL Server Profiler.
我们希望看到我们在 MS SQL Server Profiler 中看到的相同类型的信息。
Thanks for any help
谢谢你的帮助
回答by Tony Andrews
You could take a look at the DBMS_APPLICATION_INFO package. This allows you to "instrument" your PL/SQL code with whatever information you want - but it does entail adding calls to each procedure to be instrumented.
您可以查看DBMS_APPLICATION_INFO 包。这允许您使用您想要的任何信息“检测”您的 PL/SQL 代码 - 但它确实需要向每个要检测的过程添加调用。
See also this AskTom threadon using DBMS_APPLICATION_INFO to monitor PL/SQL.
另请参阅有关使用 DBMS_APPLICATION_INFO 监视 PL/SQL 的AskTom 线程。
回答by Clyde
I think you are using the word "log" in a strange manner.
我认为您以一种奇怪的方式使用“日志”一词。
We can log SQL Statements...
我们可以记录 SQL 语句...
Do you really mean to say you can TRACE sql statements with bind variables? Tony's answer is directed to the ability to LOG what you are doing. This is always superior to tracing because only you know what is important to you. Perhaps the execution of your process depends heavily on querying a value from a table. Since that value changes and it's not passed in as a parameter, you could lose that information.
你真的是说你可以用绑定变量跟踪 sql 语句吗？托尼的回答是针对记录你正在做的事情的能力。这总是优于追踪，因为只有你知道什么对你很重要。也许您的流程的执行在很大程度上取决于从表中查询值。由于该值发生变化并且它没有作为参数传入，因此您可能会丢失该信息。
But if you actually LOG what you are doing, you can include that value in your Log table and you'll know not only the variables you passed in but that key value as well.
但是如果你真的记录了你正在做的事情，你可以在你的日志表中包含那个值，你不仅会知道你传入的变量，还会知道那个键值。
alter system set events '10046 trace name context forever, level 12'; Is that what you were using?
更改系统设置事件“10046 永远跟踪名称上下文，级别 12”；那是你用的吗？
回答by Clyde
Yes, I think I should have used the term 'trace'
是的，我想我应该使用“trace”这个词
I'll try to describe what we've done:
我将尝试描述我们所做的：
Using the enterprise manager (as dbo) we've gone to a session, and started a trace
使用企业管理器（作为 dbo），我们进入了一个会话，并开始了跟踪
start trace Enable wait info, bind info
开始跟踪启用等待信息，绑定信息
Run an operation on our application that hits the DB
在我们的应用程序上运行一个命中数据库的操作
Finish the trace, run this on the output:
完成跟踪，在输出上运行：
tkprof .prc output2.txt sys=no record=record.txt explain=dbo@DBINST/PW
tkprof .prc output2.txt sys=no record=record.txt 解释=dbo@DBINST/PW
What we're wanting to see is, "these procedures were called with these parameters" What we're getting is:
我们想要看到的是，“这些过程是用这些参数调用的”我们得到的是：
Begin dbo.UPKG_PACKAGENAME.PROC(:v0, :v1, :v2 ...); End;
/
Begin dbo.UPKG_PACKAGENAME.PROC2(:v0, :v1, :v2 ...); End;
/
...
So we can trace the procedures that were called, but we don't get the actual parameter values, just the :v0, etc.
所以我们可以跟踪被调用的过程，但我们没有得到实际的参数值，只有 :v0 等。
My understanding is that what we've done is the same as the alter system statement, but please let us know if that's not the case.
我的理解是我们所做的与 alter system 语句相同，但如果不是这样，请告诉我们。
Thanks
谢谢
回答by Clyde
are you using 10g let try with this exec dbms_monitor.session_trace_enable(session_id=>xxx, serial_num=>xx, waits=>true, binds=>true); you can get session_id=SID & serial_num=SERIAL# from v$session
你在用 10g 试试这个 exec dbms_monitor.session_trace_enable(session_id=>xxx, serial_num=>xx, waits=>true, binds=>true); 你可以从 v$session 得到 session_id=SID & serial_num=SERIAL#
