Generating AWR Reports - Helper Script

REM David Mann - http://ba6.us - @ba6dotus
REM AWR for Single Instance

COLUMN DBID FORMAT 9999999999
COLUMN INS FORMAT 9999
COLUMN SNAP_ID FORMAT 999999
COLUMN BEGIN_INTERVAL_TIME FORMAT A16
COLUMN END_INTERVAL_TIME FORMAT A16
COLUMN MINUTES FORMAT 999
SELECT DBID,
       INSTANCE_NUMBER AS INS,
       SNAP_ID,
       EXTRACT(day from 24*60*(END_INTERVAL_TIME-BEGIN_INTERVAL_TIME)) AS MINUTES,
       TO_CHAR(END_INTERVAL_TIME,'YYYY-MM-DD HH24:MI') AS END_INTERVAL_TIME
  FROM DBA_HIST_SNAPSHOT 
 ORDER BY BEGIN_INTERVAL_TIME;

undefine DBID
undefine INSID
undefine STARTSNAPID
undefine ENDSNAPID


ACCEPT STARTSNAPID PROMPT "Enter begin Snap ID : " 
ACCEPT ENDSNAPID PROMPT "Enter begin Snap ID : " 

COL dat1 NEW_VALUE DBID
SELECT DBID as dat1 FROM DBA_HIST_SNAPSHOT WHERE SNAP_ID=&STARTSNAPID;

COL dat2 NEW_VALUE INSID
SELECT INSTANCE_NUMBER as dat2 FROM DBA_HIST_SNAPSHOT WHERE SNAP_ID=&STARTSNAPID;


--SELECT TO_CHAR(DBID)||'|'||TO_CHAR(INSTANCE_NUMBER)||'|'||TO_CHAR(SNAP_ID)||'|'||TO_CHAR(END_INTERVAL_TIME,'YYYY-MM-DD HH24:MI') AS "DBID|INST_NUM|SNAP_ID|END_TIME"
 --FROM dba_hist_snapshot order by BEGIN_INTERVAL_TIME;

column report_name new_value report_name;
select 'awr_'||(select NAME from v$database where DBID = &&DBID)||'_'||&&INSID||'_'||
(select to_char(END_INTERVAL_TIME,'yyyymmdd.hh24mi') from dba_hist_snapshot where SNAP_ID=&&STARTSNAPID)||'_'||
(select to_char(END_INTERVAL_TIME,'yyyymmdd.hh24mi') from dba_hist_snapshot where SNAP_ID=&&ENDSNAPID)
||'.html' report_name 
from dual;

SET FEEDBACK OFF 
SET HEADING OFF 
SET LINESIZE 32000 
SET LONG 32000 
SET LONGCHUNKSIZE 32000 
SET PAGESIZE 0 
SET SPACE 0 
SET TERMOUT OFF 
SET TRIMSPOOL ON
SET VERIFY OFF
SET SERVEROUTPUT ON
exec DBMS_OUTPUT.ENABLE(5000000);


spool &&report_name
select output from table(sys.dbms_workload_repository.awr_report_html(&DBID,&INSID,&STARTSNAPID,&ENDSNAPID));
spool off
set termout on