CODEWITHSUNDEEP
oracle-databaseoracle11gawr
user2755905
user2755905

Reputation: 21

DBA_HIST_ACTIVE_SESS_HISTORY get sql by user and object schema

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 ?

Upvotes: 1

Views: 31922

Answers (2)

Bipul Jaishwal
Bipul Jaishwal

Reputation: 323

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 :)

Upvotes: -1

Lalit Kumar B
Lalit Kumar B

Reputation: 49102

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
/

Upvotes: 0

Related Questions