是否有跟踪在 Oracle 上执行的 SQL 的工具
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5045369/
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
Is there a tool for tracing SQLs executed on Oracle
提问by f20k
Is there a tool (that already comes with Oracle) for tracing SQLs that have been executed? In DB2 there is something called an 'event monitor' which I use to track the tables that have been updated. Is there an equivalent tool in Oracle?
是否有用于跟踪已执行 SQL 的工具（Oracle 已经附带）？在 DB2 中有一个叫做“事件监视器”的东西，我用它来跟踪已更新的表。Oracle 中是否有等效的工具？
I plan to
我打算
- enable tracing
- go on the website (that uses the db) and change an entry
- disable tracing
- see output file and record which table has been updated.
- 启用跟踪
- 访问网站（使用数据库）并更改条目
- 禁用跟踪
- 查看输出文件并记录哪个表已更新。
There is a table I am looking that should be updated when the entry is changed. I do not know what the name of the table is (and there are many tables), and so I need to trace the SQL executed to find out.
我正在寻找一张表格，当条目更改时应该更新。我不知道表的名称是什么（并且有很多表），因此我需要跟踪执行的SQL才能找到。
I have tried:
我试过了：
ALTER SESSION SET sql_trace = true;
-- go on website and change an entry
ALTER SESSION SET sql_trace = false;
tkprof the_trace_file.trc file.out EXPLAIN=system/manager SYS=no
However when following those steps above, no SQLs were recorded.
但是，在执行上述步骤时，没有记录任何 SQL。
Is there a tool that Oracle provides? (I would like to avoid downloading external software)
是否有 Oracle 提供的工具？（我想避免下载外部软件）
回答by Ronnis
There is a table I am looking that should be updated when the entry is changed. I do not know what the name of the table is (and there are many tables), and so I need to trace the SQL executed to find out.
我正在寻找一张表格，当条目更改时应该更新。我不知道表的名称是什么（并且有很多表），因此我需要跟踪执行的SQL才能找到。
I'm thinking you are using the word "trace" here with another meaning than what is usually meant in the Oracle world.
我认为您在此处使用“跟踪”一词的含义不同于 Oracle 世界中通常的含义。
You basically hit some button in the app, and by looking at what SQL queries are running, you want to find what table that code was referencing? Did I get it right?
In that case, you could have a look at v$sql, and look at columns SQL_TEXT and SQL_FULLTEXT.
您基本上点击了应用程序中的某个按钮，通过查看正在运行的 SQL 查询，您想找到代码所引用的表？我做对了吗？
在这种情况下，您可以查看v$sql，并查看 SQL_TEXT 和 SQL_FULLTEXT 列。
回答by Gary Myers
The ALTER SESSION commands work at the session level (ie your current connection). The website will use a different session (probably from a connection pool). You can enable tracing for all sessions using the ALTER SYSTEM SET sql_trace = true;
ALTER SESSION 命令在会话级别（即您当前的连接）工作。该网站将使用不同的会话（可能来自连接池）。您可以使用 ALTER SYSTEM SET sql_trace = true; 为所有会话启用跟踪。
回答by Adam Musch
The main reason you didn't get anything in the trace file is because you didn't do anything in the session where trace was enabled.
您在跟踪文件中没有得到任何内容的主要原因是您没有在启用跟踪的会话中执行任何操作。
If you'd have done:
如果你已经做了：
alter system set sql_trace = true;
-- fiddle around with the website
alter system set sql_trace = false;
You'd have gotten one or more trace files, one for each session which had activity while you were fiddling with the website.
您会获得一个或多个跟踪文件，每个会话都有一个跟踪文件，这些文件在您摆弄网站时有活动。
The problem is that if the website uses connection pooling, your user activity may have been spread across several connections, and may be intermingled with other concurrent user activity.
问题是，如果网站使用连接池，您的用户活动可能已经分布在多个连接上，并且可能与其他并发用户活动混合在一起。
回答by caraca
Maybe Oracle Audit will help you.
也许 Oracle 审计会帮助你。
Here is a good explanation: http://www.oracle-base.com/articles/10g/Auditing_10gR2.php
这是一个很好的解释：http: //www.oracle-base.com/articles/10g/Auditing_10gR2.php
You have to enable audit by setting the parameter AUDIT_TRAIL.
您必须通过设置参数 AUDIT_TRAIL 来启用审计。
That is at server level. You can audit at client level using a third party sql tracer for OCI:
那是在服务器级别。您可以使用 OCI 的第三方 sql 跟踪器在客户端级别进行审计：
回答by JOTN
I find the Enterprise Manager the most useful tool for this. As has already been noted you have to alter the session that the web site is using and not your own. If you set your connection pool limit to 1 connection, you can easily find the session in the enterprise manager and then turn on the tracing. Usually a find the the top queries display in the enterprise manager tells me what queries are taking too long without having to trace anything.
我发现企业管理器是最有用的工具。如前所述，您必须更改网站正在使用的会话，而不是您自己的会话。如果将连接池限制设置为 1 个连接，则可以在企业管理器中轻松找到会话，然后打开跟踪。通常，找到显示在企业管理器中的最热门查询会告诉我哪些查询花费了太长时间而无需跟踪任何内容。
