DBA_HIST_ACTIVE_SESS_HISTORY get sql by user and object schema

Question

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 ?

Answer 1

You could join the following views -

1. DBA_HIST_ACTIVE_SESS_HISTORY
2. DBA_USERS
3. DBA_HIST_SQLTEXT

To filter the history for last 30 days, use sample_time of DBA_HIST_ACTIVE_SESS_HISTORY view.

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
/