Générer des rapports statspack

Petite procédure pour générer un nombre important de rapports statspack.

SET SERVEROUTPUT OFF
set feedback off

var delta number;
var date_fin varchar2(20);
begin
:delta:=&1;
--  :date_fin:=&2;
end;
/

create or replace procedure dba_all_reports( delta in number
                                         , fin in varchar default to_char(sysdate, 'YYYYMMDDHH24MI') ) as
begin
for i in (select snap_id
        , snap_next
        , to_char(snap_time, 'MMDD_HH24MI') rpt_name
        , instance_name
      from (select s.SNAP_ID
              , lead(s.SNAP_ID,1) over (order by s.SNAP_ID) SNAP_NEXT
              , s.STARTUP_TIME
              , lead(s.STARTUP_TIME, 1) over (order by s.SNAP_ID) STARTUP_NEXT
              , s.SNAP_TIME
              , i.INSTANCE_NAME
            from stats$snapshot s, v$instance i)
      where SNAP_NEXT=SNAP_ID+1
      and STARTUP_TIME=STARTUP_NEXT
      and snap_time between to_date(fin,'YYYYMMDDHH24MI')-delta and to_date(fin,'YYYYMMDDHH24MI') )
loop
DBMS_OUTPUT.ENABLE (32000);
dbms_output.put_line ('define begin_snap='||i.snap_id);
dbms_output.put_line ('define end_snap='||i.snap_next);
dbms_output.put_line ('define report_name='||i.instance_name||'_'||i.rpt_name);
dbms_output.put_line ('@?/rdbms/admin/spreport.sql');
end loop;
end;
/

SET SERVEROUTPUT ON
spool reports.sql
-- execute dba_all_reports(:delta, :date_fin)
execute dba_all_reports(:delta)
spool off

@reports.sql

drop procedure dba_all_reports;

Les rapports ainsi générés sont nommés <ORACLE_SID>_<MOIS><JOUR>_<HEURE><MINUTES>.lst

On peut décommenter les lignes

--  :date_fin:=&2;

et

-- execute dba_all_reports(:delta, :date_fin)

et supprimer la ligne

execute

dba_all_reports(:delta)

de manière à générer des rapports anciens.