set serveroutput on size 1000000

spool statspack_report_scripts.txt

declare

sp_exe varchar2(100);

sp_id1 varchar2(10);

sp_id2 varchar2(10);

sp_name varchar2(150);

begin

for i in 开始快照号..结束快照号 loop

select '@?/rdbms/admin/awrrpt.sql' into sp_exe from dual;

select to_char(snap_id) into sp_id1 from dba_hist_snapshot where snap_id=i;

select to_char(snap_id) into sp_id2 from dba_hist_snapshot where snap_id=i+1;

select 'AWR报告文件名前缀'||to_char(b.BEGIN_INTERVAL_TIME,'yyyymmdd')||'-'||to_char(b.BEGIN_INTERVAL_TIME,'hh24mi')||'-'||

       (select to_char(a.BEGIN_INTERVAL_TIME,'hh24mi')||'.html' from dba_hist_snapshot a where a.snap_id=b.snap_id+1)

        into sp_name

        from dba_hist_snapshot b where snap_id=i;

dbms_output.put_line(sp_exe);

dbms_output.put_line('html');

dbms_output.put_line('10'); 

dbms_output.put_line(sp_id1);

dbms_output.put_line(sp_id2);

dbms_output.put_line(sp_name);

dbms_output.new_line;

end loop;

end;

/

spool off

然后将生成的statspack_report_scripts.txt中的内容负责出来,全部贴到sqlplus中去就可以批量生成AWR报告了

下面是snapshot报告批量生成的方法

set serveroutput on size 1000000

spool statspack_report_scripts.txt

declare

sp_exe varchar2(100);

sp_id1 varchar2(10);

sp_id2 varchar2(10);

sp_name varchar2(150);

begin

for i in 快照开始号码..快照结束号码 loop

select '@?/rdbms/admin/spreport.sql' into sp_exe from dual;

select to_char(snap_id) into sp_id1 from stats$snapshot where snap_id=i;

select to_char(snap_id) into sp_id2 from stats$snapshot where snap_id=i+1;

select '报告名称前缀'||to_char(b.snap_time,'yyyymmdd')||'-'||to_char(b.snap_time,'hh24mi')||'-'||

       (select to_char(a.snap_time,'hh24mi')||'.txt' from stats$snapshot a where a.snap_id=b.snap_id+1)

        into sp_name

        from stats$snapshot b where snap_id=i;

dbms_output.put_line(sp_exe);

dbms_output.put_line(sp_id1);

dbms_output.put_line(sp_id2);

dbms_output.put_line(sp_name);

dbms_output.put_line('kongge');

dbms_output.put_line('kongge');        

end loop;

end;

/

spool off

这里有优化过的脚本

http://space.itpub.net/?uid-469356-action-viewspace-itemid-758911