set serveroutput on;
set echo off;
set feedback off;
set timing off;
set verify off;
define tempfile = _tmp.run.all.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;
high_snap NUMBER;
low_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 min(snap_id) into low_snap from dba_hist_snapshot
where dbid = dbid
and instance_number = inst_num
order by snap_id;
select max(snap_id) into high_snap from dba_hist_snapshot
where dbid = dbid
and instance_number = inst_num
order by snap_id;
begin_snap := low_snap;
while begin_snap < high_snap loop
end_snap := begin_snap + 1;
select 'awrrpt.' || db_name || '.' || inst_num || '.' || begin_snap || '-' -
|| end_snap || '.' || report_type into report_name from dual;
dbms_output.put_line('exec dbms_lock.sleep(10);');
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;');
begin_snap := begin_snap + 1;
end loop;
END;
/
spool off;
@&tempfile
exit
Generate AWR reports for all snapshots
This script will go through your AWR repository and generate an AWR report for each snapshot. Depending on the size of your repository, this may take a while to run. For example, a repository of 1 week will generate (7 * 24) 168 reports. The script sleeps 10 seconds between generating each report so it doesn't put a strain on the database.
