Historical SQL Monitor

SET long 1000000 linesize 4000 pagesize 0 head off feed off echo off termout off
repheader off

var sql_id varchar2(13)
EXEC :sql_id := '&1'

col rep_name        new_value       rep_name
SELECT '/tmp/sqlmon_' || :sql_id rep_name FROM dual;
col SQL_report format a300

spool &rep_name..txt
SELECT DBMS_AUTO_REPORT.REPORT_REPOSITORY_DETAIL(RID => report_id, TYPE => 'text') SQL_report FROM
(
SELECT * FROM
(
SELECT report_id,
       EXTRACTVALUE(XMLType(report_summary),'/report_repository_summary/sql/@sql_id') sql_id,
       to_number(EXTRACTVALUE(XMLType(report_summary),'/report_repository_summary/sql/stats/stat[@name=''elapsed_time'']')) elapsed_time,
       period_start_time
  FROM dba_hist_reports
 WHERE component_name = 'sqlmonitor'
)
WHERE sql_id = :sql_id AND period_start_time > sysdate - 5
ORDER BY elapsed_time DESC NULLS LAST
fetch FIRST 1 ROWS ONLY);
spool off

host less -S &rep_name..txt

SET long 1000000 linesize 300 pagesize 200 head ON feed ON echo ON termout ON