自動生成Statspack的指令碼

以下為Oracle資料庫，自動生成Statspack的指令碼。

可以設定在每天的23點許，一次性生成當天的資料庫Statspack Report，時間範圍為9:00～18:00的工作時間，抓取週期為1小時一次。

以下為sql的指令碼，shell部分可以根據具體OS環境確定，筆者環境為Windows 2003 Server。

[@more@]

set serveroutput on
set feedback off
set trims on

spool sp_temp.sql

declare
cursor cursnap(sdate varchar2) is
select a.snap_id beginid,
b.snap_id endid,
'Z:dbashellmonitordba_logspcas_' || substr(sdate,5) || '_' || a.snap_time || b.snap_time ||
'.log' repname
from (select rownum r, snap_id, to_char(snap_time, 'hh24') snap_time
from perfstat.stats$snapshot
where snap_time between to_date(sdate||'0830', 'yyyymmddhh24mi') and
to_date(sdate||'1730', 'yyyymmddhh24mi')
order by 2) a,
(select rownum r, snap_id, to_char(snap_time, 'hh24') snap_time
from perfstat.stats$snapshot
where snap_time between to_date(sdate||'0930', 'yyyymmddhh24mi') and
to_date(sdate||'1830', 'yyyymmddhh24mi')
order by 2) b
where a.r = b.r;
rs cursnap%rowtype;
s_date varchar2(8);
d_date number;

begin
select to_char(sysdate,'yyyymmdd') into s_date from dual;
--s_date:='20080119';
select to_char(to_date(s_date,'yyyymmdd')-1,'d') into d_date from dual;
if d_date<=5 then
open cursnap(s_date);
loop
fetch cursnap into rs;
exit when cursnap%notfound;
dbms_output.put_line('define begin_snap='||rs.beginid);
dbms_output.put_line('define end_snap='||rs.endid);
dbms_output.put_line('define report_name='||rs.repname);
dbms_output.put_line();
dbms_output.put_line('undefine begin_snap');
dbms_output.put_line('undefine end_snap');
dbms_output.put_line('undefine report_name');
end loop;
close cursnap;
end if;
dbms_output.put_line('exit');
end;
/

spool off;
exit;