Script to Generate AWR Reports for All snap_ids Between 2 Given Dates
gen_batch.sql
set echo off heading off feedback off verify off
select ‘Please enter dates in DD-MON-YYYY HH24 format:’ from dual;
select ‘You have entered:’, ‘&&BEGIN_DATE’, ‘&&END_DATE’ from dual;
set pages 0 termout off
spool batch.sql
SELECT DISTINCT ‘@pcreport ‘
||b.snap_id
||’ ‘
||e.snap_id
||’ ‘
|| TO_CHAR(b.end_interval_time,’YYMMDD_HH24MI_’)
||TO_CHAR(e.end_interval_time,’HH24MI’)
||’.txt’ Commands,
‘– ‘||TO_CHAR(b.end_interval_time,’YYMMDD_HH24MI’) lineorder
FROM dba_hist_snapshot b,
dba_hist_snapshot e
WHERE b.end_interval_time>=to_date(‘&BEGIN_DATE’,’DD-MON-YYYY HH24′)
AND b.end_interval_time<=to_date(‘&END_DATE’,’DD-MON-YYYY HH24′) AND e.snap_id =b.snap_id+1 ORDER BY lineorder / spool off set termout on select ‘Generating Report Script batch.sql…..’ from dual; select ‘Report file created for snap_ids between:’, ‘&&BEGIN_DATE’, ‘&&END_DATE’, ‘Check file batch.sql’ from dual; set echo on termout on verify on heading on feedback on pcreport.sql define num_days = 0; define report_type = ‘text’ column inst_num new_value inst_num column dbname new_value dbname column dbid new_value dbid SELECT d.dbid dbid , d.name db_name , i.instance_number inst_num , i.instance_name inst_name FROM v$database d, v$instance i; column begin_snap new_value begin_snap column end_snap new_value end_snap column report_name new_value report_name SELECT &1 begin_snap FROM dual; SELECT &2 end_snap FROM dual; SELECT name ||’_’ ||’&3′ report_name FROM v$database; @@?/rdbms/admin/awrrpti run as sysdba SQL> @gen_batch
SQL> set echo off heading off feedback off verify off
Please enter dates in DD-MON-YYYY HH24 format:
once complete than run
SQL> @batch
