为了分析这几天数据库状况，进行statspack report快照统计信息分析，特此编写如下脚本方便执行收集所需信息。
alter session set nls_date_format='yyyymmdd-hh24miss';
select snap_id,snap_time from stats$snapshot order by snap_id;
--查询从哪天哪个快照开始执行，准备执行收集到哪天哪个快照。
--这里为收集1400到1406共7组快照统计信息（快照每半小时执行一次），即2007年08月16日-13：00～2007年08月16日-16：30的快照信息。
set serveroutput on
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 1400..1406 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 'hnmyz1_sp_'||to_char(b.snap_time,'yyyymmdd')||'-'||to_char(b.snap_time,'hh24mi')||'-'||
(select to_char(a.snap_time,'hh24mi') 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);
end loop;
end;
/
spool off
输出结果：
17:17:37 23 /
@?/rdbms/admin/spreport.sql
1400
1401
hnmyz1_sp_20070816-1300-1330
@?/rdbms/admin/spreport.sql
1401
1402
hnmyz1_sp_20070816-1330-1400
@?/rdbms/admin/spreport.sql
1402
1403
hnmyz1_sp_20070816-1400-1430
@?/rdbms/admin/spreport.sql
1403
1404
hnmyz1_sp_20070816-1430-1500
@?/rdbms/admin/spreport.sql
1404
1405
hnmyz1_sp_20070816-1500-1530
@?/rdbms/admin/spreport.sql
1405
1406
hnmyz1_sp_20070816-1530-1600
@?/rdbms/admin/spreport.sql
1406
1407
hnmyz1_sp_20070816-1600-1630
PL/SQL procedure successfully completed.
17:17:37 SQL> spool off