SQL Script to format AWR Reports
set head off feed off ver off echo off
set pages 0
clear break compute;
repfooter off;
ttitle off;
btitle off;
set heading on;
set timing off veri off space 1 flush on pause off termout on numwidth 10;
set echo off feedback off pagesize 60 linesize 80 newpage 1 recsep off;
set trimspool on trimout on define “&” concat “.” serveroutput on;
set underline on;
col dbid new_value v_dbid noprint
col instance_number new_value v_inst_num noprint
select dbid from v$database;
select instance_number from v$instance;
variable dbid number;
variable inst_num number;
begin
:dbid := &v_dbid;
:inst_num := &v_inst_num;
end;
/
define num_days=’1′
set termout on;
column instart_fmt noprint;
column inst_name format a12 heading ‘Instance’;
column db_name format a12 heading ‘DB Name’;
column snap_id format 99999990 heading ‘Snap Id’;
column snapdat format a18 heading ‘Snap Started’ just c;
column lvl format 99 heading ‘Snap|Level’;
column lvl format 99 heading ‘Snap|Level’;
– List available snapshots
break on inst_name on db_name on host on instart_fmt skip 1;
ttitle off;
set head off
–
– Set up the binds for max snap id
col snap_id new_value v_snap_id noprint
select
max(s.snap_id) snap_id
from dba_hist_snapshot s
, dba_hist_database_instance di
where s.dbid = :dbid
and di.dbid = :dbid
and s.instance_number = :inst_num
and di.instance_number = :inst_num
and di.dbid = s.dbid
and di.instance_number = s.instance_number
and di.startup_time = s.startup_time
/
variable snap_id number;
begin
:snap_id := &v_snap_id;
end;
/
prompt DBID: &v_dbid
prompt INST_NUM: &v_inst_num
prompt MAX_SNAP_ID: &v_snap_id
define inst_num = &v_inst_num;
define num_days = 1;
define inst_name = ‘Instance’;
define db_name = ‘Database’;
define dbid = &v_dbid;
define begin_snap = &v_snap_id-1;
define end_snap = &v_snap_id
define report_type = ‘html’;
define report_name = /tmp/awr_output.html
@?/rdbms/admin/awrrpti.sql
exit;