The DBA_HIST_ACTIVE_SESS_HISTORY data dictionary view can be used to find queries executed in the Oracle database. Historical information is available.<\/p>\n
You can use the V$ACTIVE_SESSION_HISTORY view for currentyl running queries.<\/p>\n
For example, a query such as the following can be used to find queries executed in the last 30 days.<\/p>\n\n
SELECT\nh.sample_time,\nu.username,\nh.program,\nh.module,\ns.sql_text\nFROM\nDBA_HIST_ACTIVE_SESS_HISTORY h,\nDBA_USERS u,\nDBA_HIST_SQLTEXT s\nWHERE sample_time >= SYSDATE - 30\nAND h.user_id=u.user_id\nAND h.sql_id = s.sql_iD\nORDER BY h.sample_time<\/pre>\nYou can also specify a specific date range for sample_time in the query above.<\/p>\n
SELECT\n h.sample_time,\n u.username,\n h.program,\n h.module,\n s.sql_text\nFROM\n DBA_HIST_ACTIVE_SESS_HISTORY h,\n DBA_USERS u,\n DBA_HIST_SQLTEXT s\nWHERE sample_time between to_date('01\/10\/2018 00:00:00','DD\/MM\/YYYY HH24:MI:SS') \nand to_date('15\/10\/2018 00:00:00','DD\/MM\/YYYY HH24:MI:SS')\n AND h.user_id=u.user_id\n AND h.sql_id = s.sql_iD\nORDER BY h.sample_time<\/pre>\n<\/div>\n