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 ?

Why do you want this information? AWR is useful for performance tuning but it's sampling makes it unreliable for thorough code analysis. — Jon Heller, Feb 01 '15 at 07:46
I need to do usage analysis that who is accessing and what objects for a given schema in last 30 -60 days. Cant use auditing so looking for options — user2755905, Feb 07 '15 at 12:31

score 1 · Answer 1 · answered Jan 29 '15 at 05:39

1

You could join the following views -

DBA_HIST_ACTIVE_SESS_HISTORY
DBA_USERS
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
/

answered Jan 29 '15 at 05:39

Lalit Kumar B

47,486
13
97
124

This doesnt answer the question which is Analyze all SQL in past 30 days and report usage of all objects used in a given schema? include which user if possible. SQL could be using schema.table or synonym , packages/function/procs, pl/sql code, triggers etc – user2755905 Jan 31 '15 at 13:31
Ok. Can you elaborate with a small example. – Lalit Kumar B Jan 31 '15 at 13:44

score 0 · Answer 2 · edited Apr 19 '21 at 13:07

The very best and simplest way to fetch related data using below query.

 SELECT H.SAMPLE_TIME,
         U.USERNAME,
         H.PROGRAM,
         H.MODULE,
         S.SQL_TEXT,
         H.SQL_ID,
         H.TOP_LEVEL_SQL_ID,
         H.BLOCKING_SESSION_STATUS
    FROM DBA_HIST_ACTIVE_SESS_HISTORY H, DBA_USERS U, DBA_HIST_SQLTEXT S
   WHERE     H.SAMPLE_TIME >= SYSDATE - 30
         AND H.SQL_ID = S.SQL_ID 
  --AND H.PROGRAM IN ('Toad.exe', 'SQL Developer')
 --AND U.USERNAME ='YOUR_USERNAME'
ORDER BY H.SAMPLE_TIME DESC

In the above code you can also fetch data based on your requirements as below.

1. Custom user data: Just modify YOUR_USERNAME with your real username.

2. Program: Program name can be anything like SQL Developer or JDBC Thin client to identify from which client the queries are getting triggered, but optional.

Hope it will help and answer to your question. Thanks :)

DBA_HIST_ACTIVE_SESS_HISTORY get sql by user and object schema

2 Answers2