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.