Automating the removal of statspack snapshots

In case you find yourself in one of those shops with the good old statspack!!
Every child likes new toys but don’t throw your dummy out yet,statspack is still good.
It does eat up space if you set it to run regularly and you don’t want to make your already miserable life difficult by manually deleting snapshots.
You can be proactive by setting up a procedure to do the cleaning job for you.
Try this:

##I want to delete all snapshots that are older than 14 days in a RAC cluster for a particuler instance.
##Remember in RAC each instance has it’s own snapshot

create or replace procedure perfstat.purge_snaps as
max_snap number;
min_snap number;
database_id number;
inst_no number;
snap_number number;
ret number ;

ret := 0;
select instance_number into inst_no from v$instance;
select count(*) into ret from stats$snapshot where instance_number=inst_no and snap_time 0 then

select min(s.snap_id) , max(s.snap_id),max(di.dbid) into min_snap,max_snap,database_id
from stats$snapshot s , stats$database_instance di
where s.dbid= di.dbid
and s.instance_number= di.instance_number
and s.instance_number=inst_no
and di.startup_time = s.startup_time
and s.snap_time min_snap, i_end_snap =>max_snap, i_snap_range => true, i_extended_purge => false, i_dbid=> database_id , i_instance_number=> inst_no);

dbms_output.Put_line(‘The number of snapshots deleted are ‘||to_char(snap_number));

end if;
end; /


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s