STATSPACK provides a purge utility to remove the older snapshots. sppurge.sql script can be invoked to remove specific snapshots.
You will need to specify begin and end snapid for the script or you can modify it to select all the snapids for a snap_time date range.
You can setup a small batch job to run daily to purge the snapshots older than certain time period, to free up perfstat tablespace.
$ORACLE_HOME/bin/sqlplus -s “perfstat/perfstat” << EOF
TTITLE OFF
SET HEADING OFF
SET FEEDBACK OFF
SET PAGES 0
SET SERVEROUTPUT ON SIZE 1000000
SET VERIFY OFF
WHENEVER SQLERROR EXIT 8
WHENEVER OSERROR EXIT 9
spool $HOME/logs/sp_purge_db1.log
@$HOME/scripts/statspack/sp_purge.sql
commit;
spool off
exit;
EOF
if [ `wc -l ${HOME}/logs/sp_purge_db1.log awk '{print $1}'` -gt 0 ]
then
/bin/mail -s “Error” email_dba < ${HOME}/logs/sp_purge_db1.log
fi
sp_purge.sql can modified to specify the required date range for the snapshots to be purged
———————-
———-
——————
column min_snap_id new_val LoSnapId
column max_snap_id new_val HiSnapId
select min(s.snap_id) min_snap_id, max(s.snap_id) max_snap_id
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.startup_time = s.startup_time
and s.snap_time < sysdate – 60;
–
– Post warning
prompt
prompt
prompt Warning
prompt ~~~~~~~
prompt sppurge.sql deletes all snapshots ranging between the lower and
prompt upper bound Snapshot Id’s specified, for the database instance
prompt you are connected to.
prompt
prompt You may wish to export this data before continuing.
prompt
–
– Obtain snapshot ranges
prompt
prompt Specify the Lo Snap Id and Hi Snap Id range to purge
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt Using &&LoSnapId for lower bound.
prompt
prompt Using &&HiSnapId for upper bound.
variable lo_snap number;
variable hi_snap number;
begin
:lo_snap := &losnapid;
:hi_snap := &hisnapid;
end;
/
set termout off
–
– Get begin and end snapshot times – these are used to delete undostat
column btime new_value btime
column etime new_value etime
select to_char(snap_time, ‘YYYYMMDD HH24:MI:SS’) btime
from stats$snapshot b
where b.snap_id = :lo_snap
and b.dbid = :dbid
and b.instance_number = :inst_num;
select to_char(snap_time, ‘YYYYMMDD HH24:MI:SS’) etime
from stats$snapshot e
where e.snap_id = :hi_snap
and e.dbid = :dbid
and e.instance_number = :inst_num;
variable btime varchar2(25);
variable etime varchar2(25);
begin
:btime := ‘&btime’;
:etime := ‘&etime’;
end;
/
set termout on
set heading off
select ‘WARNING: LoSnapId or HiSnapId specified does not exist in STATS$SNAPSHOT’
from dual
where not exists
(select null
from stats$snapshot
where instance_number = :inst_num
and dbid = :dbid
and snap_id = :lo_snap)
or not exists
(select null
from stats$snapshot
where instance_number = :inst_num
and dbid = :dbid
and snap_id = :hi_snap);
set heading on
———————–
——————
———
