自动生成Statspack的脚本

http://aluocp.itpub.net/post/17417/453010

以下为Oracle数据库，自动生成Statspack的脚本。

可以设置在每天的23点许，一次性生成当天的数据库Statspack Report，时间范围为9:00～18:00的工作时间，抓取周期为1小时一次。

以下为sql的脚本，shell部分可以根据具体OS环境确定，笔者环境为Windows 2003 Server。

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;