oracle DBA_HIST_ACTIVE_SESS_HISTORY 按用户和对象模式获取 sql
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28207365/
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
DBA_HIST_ACTIVE_SESS_HISTORY get sql by user and object schema
提问by user2755905
Hi I am learning ASH and AWR tables but any ideas as to how i can get list of sql, objects and schema owner accessed by a give user in last 30 days ? Basically get all SQL text, and then search within this SQL to see if a given object (table, package, function, view etc ) is accessed for a given schema and by which user ? Any ideas suggestion on where and how to start ?
嗨，我正在学习 ASH 和 AWR 表，但是关于如何获取给定用户在过去 30 天内访问的 sql、对象和架构所有者列表的任何想法？基本上获取所有 SQL 文本，然后在此 SQL 中搜索以查看是否为给定模式访问给定对象（表、包、函数、视图等）以及哪个用户？关于从哪里开始以及如何开始的任何想法建议？
回答by Lalit Kumar B
You could join the following views -
你可以加入以下观点——
- DBA_HIST_ACTIVE_SESS_HISTORY
- DBA_USERS
- DBA_HIST_SQLTEXT
- DBA_HIST_ACTIVE_SESS_HISTORY
- DBA_USERS
- DBA_HIST_SQLTEXT
To filter the history for last 30 days, use sample_timeof DBA_HIST_ACTIVE_SESS_HISTORYview.
要筛选最近30天的历史，使用sample_time的DBA_HIST_ACTIVE_SESS_HISTORY视图。
Something like -
就像是 -
SELECT
h.sample_time,
u.username,
h.program,
h.module,
s.sql_text
FROM
DBA_HIST_ACTIVE_SESS_HISTORY h,
DBA_USERS u,
DBA_HIST_SQLTEXT s
WHERE sample_time >= SYSDATE - 30
AND h.user_id=u.user_id
AND h.sql_id = s.sql_iD
ORDER BY h.sample_time
/
