Statspack Purge script scheduled

Statspack Purge script scheduled in Oracle

Two Scripts provided below to scheduled the statspack purging
 
Fist way
Script will auto create the job in dba_jobs for purging statspack snapshot after 15 day.
you can change num_days value to increase the snapshot retention, Weekly

1. Run following script will create and scheduled job in dba_jobs and return job number.

set serveroutput on
variable v_jobno number;
begin
dbms_job.submit(:v_jobno,'statspack.purge(i_num_days=>15,i_extended_purge=>TRUE);',trunc(sysdate)+1,'trunc(sysdate)+7');
commit;
end;
/

2. You can check job by

select * from dba_jobs;

Second Ways

1. Check the snap present before 20 days

select count(*) from stats$snapshot where snap_time < sysdate-20;

2. Create the procedure which will purge statspack snapshot after 20 days gap.

create or replace procedure statspackpurge is
var_lo_snap number;
var_hi_snap number;
var_db_id number;
var_instance_no number;
noofsnapshot number;
n_count number ;
begin
n_count := 0;
select count(*) into n_count from stats$snapshot where snap_time 0 then
select min(s.snap_id) , max(s.snap_id),max(di.dbid),max(di.instance_number) into  var_lo_snap, var_hi_snap,var_db_id,var_instance_no
from stats$snapshot s
, stats$database_instance di
where s.dbid              = di.dbid
and s.instance_number   = di.instance_number
and di.startup_time     = s.startup_time
and s.snap_time var_lo_snap
, i_end_snap        => var_hi_snap
, i_snap_range      => true
, i_extended_purge  => false
, i_dbid            => var_db_id
, i_instance_number => var_instance_no);dbms_output.Put_line('snapshot deleted'||to_char(noofsnapshot));

end if;
end;
/

3. Check the job already exits in the database

SELECT  job, next_date, next_sec, failures, broken, SUBSTR(what,1,40) DESCRIPTION FROM dba_jobs;

4. Scheduled the  job at mid night

declare
my_job number;
begin
dbms_job.submit(job => my_job,
what => 'statspackpurge;',
next_date => trunc(sysdate)+1,
interval => 'trunc(sysdate)+1');
end;
/

For Statspack configure and scheduled job go to the link:
Statspack Configure
 

Advertisements

One thought on “Statspack Purge script scheduled

Leave a Reply

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

WordPress.com Logo

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

Google+ photo

You are commenting using your Google+ 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

This site uses Akismet to reduce spam. Learn how your comment data is processed.