按用户查看 SQL 历史记录 [Oracle]
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/46343896/
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
View SQL History by User [Oracle]
提问by need_java
I'm trying to find all (available) SQL history by user in our Oracle 11g database. I've tried using some view such as v$sql_monitor, v$sqlarea, and dba_hist_active_sess_historyto try to get usernames, and their executed SQL statements (joined on SID and Serial #) but I'm not having any luck. Our senior DBA and DBE said they've done it before but just told me to look in sqlarea since that has the longest history of SQL held. I'm not having any luck with this. Is this possible to do in Oracle?
我正在尝试在我们的 Oracle 11g 数据库中按用户查找所有（可用）SQL 历史记录。我已经使用了一些观点，如尝试v$sql_monitor，v$sqlarea和dba_hist_active_sess_history试图获得用户名，他们执行的SQL语句（加入了对SID和Serial＃），但我没有任何运气。我们的高级 DBA 和 DBE 说他们以前做过，但只是告诉我查看 sqlarea，因为它拥有最长的 SQL 历史。我对此没有任何运气。这可以在 Oracle 中实现吗？
Edit: We use SQL Developer. I understand that TOAD may or may not have this feature built-in but I haven't been able to find anything that accomplishes this (other than view current sessions and current SQL) in SQL Developer.
编辑：我们使用 SQL Developer。我知道 TOAD 可能有也可能没有内置此功能，但我无法在 SQL Developer 中找到任何可以实现此功能的内容（除了查看当前会话和当前 SQL）。
回答by Cyrille MODIANO
You can try something like that if you have the proper licensing to query dba_hist_active_sess_history, you need a license for the diagnostic pack:
如果您有适当的许可来查询 dba_hist_active_sess_history，您可以尝试类似的操作，您需要诊断包的许可：
select trunc(hist.sample_time,'DD'),u.name,hist.sql_id,sql.sql_text
from dba_hist_active_sess_history hist,
dba_hist_sqltext sql,
user$ u
where hist.sql_id = sql.sql_id
and hist.user_id = u.user#;
回答by bernhard.weingartner
I think the only way to fetch the exact SQL-Executions of a User (i think you mean Session?) is by enabeling Tracing. I think this article (https://oracle-base.com/articles/misc/sql-trace-10046-trcsess-and-tkprof) gives you a good instruction how it is used.
我认为获取用户的确切 SQL 执行（我认为你是说会话？）的唯一方法是启用跟踪。我认为这篇文章 ( https://oracle-base.com/articles/misc/sql-trace-10046-trcsess-and-tkprof) 为您提供了一个很好的使用说明。
