有没有一个工具可以跟踪Oracle上执行的SQL
是否有一个工具（Oracle 已附带）用于跟踪已执行的 SQL？在 DB2 中，有一个称为“事件监视器”的东西，我用它来跟踪已更新的表。 Oracle中有类似的工具吗？
我计划
- 在网站上启用跟踪
- （使用数据库）并更改条目
- 禁用跟踪
- 查看输出文件并记录哪个表已更新。
我正在寻找一个表格，当条目更改时应该更新该表格。我不知道表的名称是什么（而且有很多表），所以我需要跟踪执行的 SQL 来找出答案。
我已经尝试过：
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
但是，当执行上述步骤时，没有记录 SQL。
Oracle有提供工具吗？ （我想避免下载外部软件）
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?
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.
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.
Is there a tool that Oracle provides? (I would like to avoid downloading external software)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
我认为您在这里使用的“跟踪”一词具有不同于 Oracle 世界中通常含义的含义。
您基本上点击应用程序中的某个按钮，然后通过查看正在运行的 SQL 查询，您想找到代码引用的表？我做对了吗？
在这种情况下，您可以查看 v$sql ，然后查看 SQL_TEXT 和 SQL_FULLTEXT 列。
I'm thinking you are using the word "trace" here with another meaning than what is usually meant in the Oracle world.
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.
ALTER SESSION 命令在会话级别（即您当前的连接）工作。
该网站将使用不同的会话（可能来自连接池）。
您可以使用 ALTER SYSTEM SET sql_trace = true; 启用对所有会话的跟踪。
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;
您没有在跟踪文件中获取任何内容的主要原因是您没有在启用跟踪的会话中执行任何操作。
如果您这样做了：
您将获得一个或多个跟踪文件，每个跟踪文件对应您在摆弄网站时进行活动的每个会话。
问题是，如果网站使用连接池，您的用户活动可能分布在多个连接上，并且可能与其他并发用户活动混合在一起。
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:
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.
也许 Oracle Audit 会对您有所帮助。
这是一个很好的解释： http://www.oracle-base.com/articles /10g/Auditing_10gR2.php
您必须通过设置参数 AUDIT_TRAIL 来启用审核。
那是在服务器级别。您可以使用 OCI 的第三方 sql 跟踪器在客户端级别进行审核：
http://sourceforge.net/projects/ocimonitor /
Maybe Oracle Audit will help you.
Here is a good explanation: http://www.oracle-base.com/articles/10g/Auditing_10gR2.php
You have to enable audit by setting the parameter AUDIT_TRAIL.
That is at server level. You can audit at client level using a third party sql tracer for OCI:
http://sourceforge.net/projects/ocimonitor/
我发现企业管理器是最有用的工具。正如已经指出的，您必须更改网站正在使用的会话，而不是您自己的会话。如果将连接池限制设置为 1 个连接，则可以轻松地在企业管理器中找到该会话，然后打开跟踪。通常，企业管理器中显示的热门查询的查找结果会告诉我哪些查询花费的时间太长，而无需跟踪任何内容。
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.