做了一个脚本sprpt_batch.sh:
read line
snap_i_id=$1
end_snap=$2
sqlplus -s /nolog<<EOF
conn /as sysdba;
define begin_snap=${snap_i_id};
define end_snap=${end_snap};
define report_name=sprpt_batch_${snap_i_id}_${end_snap}.txt
set echo off
set feedback off
@myspreport
exit
EOF
将$ORACLE_HOME/rdbms/admin/spreport.sql和$ORACLE_HOME/rdbms/admin/sprepins.sql拷贝到工作目录下，重命名成myspreport.sql和mysprepins.sql
将mysprepins.sql中的部分注释掉：
/*
select to_char(s.startup_time,' dd Mon "at" HH24:mi:ss') instart_fmt
, di.instance_name inst_name
, di.db_name db_name
, s.snap_id snap_id
, to_char(s.snap_time,'dd Mon YYYY HH24:mi') snapdat
, s.snap_level lvl
, substr(s.ucomment, 1,60) commnt
from stats$snapshot s
, stats$database_instance di
where s.dbid = :dbid
and di.dbid = :dbid
and s.instance_number = :inst_num
and di.instance_number = :inst_num
and di.dbid = s.dbid
and di.instance_number = s.instance_number
and di.startup_time = s.startup_time
order by db_name, instance_name, snap_id;
*/
因为这一部分是显示statspack的保存记录的时间和snap id的。我们做成脚本，就不需要让他们显示了。
(一)
取单个statspack，可以执行：
sh sprpt_batch.sh <begin_snap_id> <end_snap_id>
如：
sh sprpt_batch.sh 118712 118713
（二）
如果要取最近30天的statspack，每隔15分钟为间隔，可以在数据库中
sqlplus "/ as sysdba"
Set line 300
Set pages 10000
Spool exec_script.sh
select script_text
from (select 'sh sprpt_batch.sh ' || lag(snap_id) over(partition by startup_time order by snap_id) || ' ' || snap_id || chr(10) || chr(10) as script_text,
lag(snap_id) over(partition by startup_time order by snap_id) as last_value,
a.*
from STATS$SNAPSHOT a
order by snap_id desc
)
where last_value is not null and snap_time>=sysdate-30
spool off
生成出来的结果如下：
sh sprpt_batch.sh 119082 119083
sh sprpt_batch.sh 119081 119082
sh sprpt_batch.sh 119080 119081
sh sprpt_batch.sh 119079 119080
……
然后执行这个exec_script.sh脚本，就可以批量的生成statspack了。
（三）如果需要每个1小时一个statspack，这个也可以做到，只需将snap_id排序，mod取4整除，（因为每4个snap id是一个小时）。
见下：
select 'sh sprpt_batch.sh ' || last_value || ' ' || snap_id || chr(10) ||
chr(10) as script_text
from (select lag(snap_id) over(partition by startup_time order by snap_id) as last_value,
kk.*
from (select mod(rank()
over(partition by startup_time order by snap_id),
4) as by_hour,
a.*
from STATS$SNAPSHOT a) kk
where by_hour = 1)
where last_value is not null
and snap_time >= sysdate - 30