如何按用户名查找最后 10 个 oracle 查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5383381/
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 find last 10 oracle queries by username?
提问by jake
Not sure if this is possible. I'm on 10g. The end result would look like this:
不确定这是否可能。我是10g。最终结果如下所示：
Username | Date/Time | sqltext
jdoe | 3/21/11 10:32:27 | select sum(total) from sales where in_date > '08-JAN-11'
jdoe | 3/21/11 10:32:21 | delete from products_old
jdoe | 3/21/11 10:32:18 | select item, description from products where item = 'blah'
jdoe | 3/21/11 10:32:06 | select count(item) from products
jdoe | 3/21/11 10:31:44 | describe products
It looks like v$sql stores almost(?) all of the sql queries ever sent, but what do I join that to to get a username, and date?
看起来 v$sql 存储了几乎（？）所有发送过的 sql 查询，但是我如何将其加入以获取用户名和日期？
回答by Justin Cave
If you have the enterprise edition and the performance and tuning pack (otherwise querying the AWR tables violates your license), the V$ACTIVE_SESSION_HISTORYview will be the closest you'll get. This will capture at each second what each active session was executing. If you have a user that is executing many SQL statements per second or your SQL statements are fast enough that they aren't active across a particular second boundary, however, not every query would be captured. If you're just trying to get a general sampling of the last 10 things that a particular user has been done (with a bias to catching longer running queries), the AWR should be sufficient. But if you are trying to do something like trace what a user is doing in their session, the AWR would not be appropriate.
如果您拥有企业版以及性能和调优包（否则查询 AWR 表将违反您的许可），该V$ACTIVE_SESSION_HISTORY视图将是您获得的最接近的视图。这将每秒捕获每个活动会话正在执行的内容。但是，如果您的用户每秒执行许多 SQL 语句，或者您的 SQL 语句速度足够快，以至于它们在特定的第二个边界内不处于活动状态，则并非每个查询都会被捕获。如果您只是想对特定用户已完成的最后 10 件事进行一般抽样（倾向于捕获更长时间运行的查询），那么 AWR 就足够了。但是，如果您尝试执行诸如跟踪用户在其会话中正在执行的操作之类的操作，则 AWR 将不合适。
If you want to capture absolutely everything a user does, you would need to trace the session. That will cause a rather voluminous trace file to be generated on the server which you can summarize using the tkprofutility. But that requires that you enable tracing for a particular session before the SQL of interest is executed-- it's not something that can be done retroactively.
如果您想绝对捕获用户所做的一切，则需要跟踪会话。这将导致在服务器上生成相当多的跟踪文件，您可以使用该tkprof实用程序对其进行汇总。但这要求您在执行感兴趣的 SQL 之前启用对特定会话的跟踪——这不是可以追溯完成的。
If you were interested only in the changes the session made, you could use LogMiner to go through the redo logs to see what the user was doing. That can be done retroactively but since SELECT statements don't generate REDO, they wouldn't be written to the redo logs and would be invisible to LogMiner.
如果您只对会话所做的更改感兴趣，您可以使用 LogMiner 浏览重做日志以查看用户在做什么。这可以追溯完成，但由于 SELECT 语句不生成重做，它们不会被写入重做日志并且对 LogMiner 不可见。
回答by vlfig
If you're using JDBC, you can always use the Log4jdbc wrapper library. It logs all jdbc activity to - you guessed - log4j.
如果您使用的是 JDBC，则始终可以使用Log4jdbc 包装器库。它将所有 jdbc 活动记录到 - 您猜对了 - log4j。
You can configure the log level for several event types, it shows the sql issued (with bind variables replaced with their values) and timing information. I use it all the time.
您可以为多种事件类型配置日志级别，它显示发出的 sql（绑定变量替换为其值）和计时信息。我用它所有的时间。
