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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/469356/viewspace-758910/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/469356/viewspace-758910/