How To Find Query History On Oracle

dbtut

5 years ago

The DBA_HIST_ACTIVE_SESS_HISTORY data dictionary view can be used to find queries executed in the Oracle database. Historical information is available.

You can use the V$ACTIVE_SESSION_HISTORY view for currentyl running queries.

For example, a query such as the following can be used to find queries executed in the last 30 days.

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

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

You can also specify a specific date range for sample_time in the query above.

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 between to_date('01/10/2018 00:00:00','DD/MM/YYYY HH24:MI:SS') 
and to_date('15/10/2018 00:00:00','DD/MM/YYYY HH24:MI:SS')
   AND h.user_id=u.user_id
   AND h.sql_id = s.sql_iD
ORDER BY h.sample_time