Is there any other way to track the long running queries from past 2 months? For example, that took more than 5 minutes? I am using the _dba_hist_snapshot_but because of the default snapshot configuration, I am getting only past few days of data. I am posting the query below that might be useful for someone else. If there is anyother way please do let me know.
SELECT stat.sql_id, parsing_schema_name "SCHEMA", to_char(ss.begin_interval_time,'dd-mm-yy hh24:mi:ss') "BEGIN_TIME", to_char(ss.end_interval_time,'dd-mm-yy hh24:mi:ss') "END_TIME", round(elapsed_time_total/1000000,0) "SECONDS", stat.executions_total, dbms_lob.substr(sql_text,4000,1) "QUERY"
FROM dba_hist_sqlstat stat, dba_hist_sqltext txt, dba_hist_snapshot ss
WHERE stat.sql_id = txt.sql_id
AND stat.dbid = txt.dbid
AND ss.dbid = stat.dbid
AND ss.instance_number = stat.instance_number
AND stat.snap_id = ss.snap_id
-- AND parsing_schema_name IN ('USER_A','USER_B')
AND ss.begin_interval_time >= to_date('01-09-21', 'MM-DD-YY')
AND stat.elapsed_time_total/1000000 >= 3600 -- in microseconds (1 second = 1000000 microseconds)
ORDER BY end_time desc;