Batch statspack scripts in oracle

Make a script 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_name1_sprpt_batch_1_snap_ I _id1__1_end_snap1_.txt
Set echo off
Set feedback off
@ Myspreport
Exit
EOF
Copy $ ORACLE_HOME/rdbms/admin/spreport. SQL and $ ORACLE_HOME/rdbms/admin/sprepins. SQL to the working directory and rename them to myspreport. SQL and mysprepins. SQL.

Comment out some of mysprepins. SQL:

/*
Select to_char (s. startup_time, 'DD Mon "at" HH24: mi: SS') instart_fmt
, Di. instance_name inst_name
, Di. db_name
, S. 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;
*/
This part shows the time and snap id of the statspack record. We do not need to display the script.

(1)

Take a single statspack and execute:

Sh sprpt_batch.sh <begin_snap_id> <end_snap_id>
For example:

Sh sprpt_batch.sh 118712 118713

(2)
If you want to obtain the statspack of the last 30 days, the interval is 15 minutes.

Sqlplus "/as sysdba"
Set line 300
Sets 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
Order by snap_id desc
        )
Where last_value is not null and snap_time >=sysdate-30
Spool off

The generated results are as follows:

Sh sprpt_batch.sh 119082 119083
Sh sprpt_batch.sh 119081 119082
Sh sprpt_batch.sh 119080 119081
Sh sprpt_batch.sh 119079 119080
......

Run the exec_script.sh script to generate statspack in batches.

(3) If you need a statspack every hour, you can also sort the snap_id and mod to perform 4 division (because every 4 snap IDs is an hour ).
See:

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