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 ;
begin
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; /
charlesajah 