Run AWR Report Daily

This script can be run from cron. It will generate an AWR report for the last 24 hours.

set serveroutput on;
set echo off;
set feedback off;
set timing off;
set verify off;

define tempfile = _tmp.run.awr.sql

spool &tempfile;

DECLARE
     db_name v$database.name%TYPE;
     dbid v$database.dbid%TYPE;
     inst_num v$instance.instance_number%TYPE;
     inst_name v$instance.instance_name%TYPE;
     begin_snap NUMBER;
     end_snap NUMBER;
     num_days NUMBER;
     report_type VARCHAR2(4);
     report_name VARCHAR2(100);
BEGIN

     report_type := 'html';
     num_days := 0;

     select instance_name, instance_number
       into inst_name, inst_num from v$instance;

     select dbid, name
       into dbid, db_name from v$database;

     select max(snap_id)
       into begin_snap
       from dba_hist_snapshot
       where begin_interval_time <= (select sysdate - 1 from dual)
         and dbid = dbid
         and instance_number = inst_num
       order by snap_id;

     select max(snap_id) into end_snap from dba_hist_snapshot order by snap_id;

     select 'awrrpt.' || db_name || '.' || inst_num || '.' || begin_snap || '-' || -
end_snap || '.' || report_type into report_name from dual;

     dbms_output.put_line('define inst_num = ' || inst_num || ';');
     dbms_output.put_line('define num_days = ' || num_days || ';');
     dbms_output.put_line('define inst_name = ' || inst_name || ';');
     dbms_output.put_line('define db_name = ' || db_name || ';');
     dbms_output.put_line('define dbid = ' || dbid || ';');
     dbms_output.put_line('define begin_snap = ' || begin_snap || ';');
     dbms_output.put_line('define end_snap = ' || end_snap || ';');
     dbms_output.put_line('define report_type = ' || report_type || ';');
     dbms_output.put_line('define report_name = ' || report_name || ';');
     dbms_output.put_line('@@?/rdbms/admin/awrrpt.sql;');

END;
/

spool off;

@&tempfile

exit