Tag Archives: Scheduled in DBA JOBS

How to Schedule Statspack Purging Jobs

1 Reply

Script to schedule the statspack purging job in Oracle

Oracle provide many alternative to purge the statspack job.

For manual statspack purging following script need to run for snapshot purging:

-- Script will ask you the snapshot id from which to which you want to delete
select min(s.snap_id) , max(s.snap_id),max(di.dbid),max(di.instance_number) 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;

-- Purge script
SQL> @?/rdbms/admin/sppurge;

-- Another way to delete manually is run the following procedure
-- Purge procedure 
exec statspack.purge(Numberofdays);

-- to purge all snapshot older than 15 days.
exec statspack.purge(15);

Scheduled statspack snapshot purging job in dbms_scheduler jobs

  1. Create a scheduled job in Oracle
BEGIN
dbms_scheduler.create_job(
job_name => 'SNAPSHOTPURGE'
,job_type => 'PLSQL_BLOCK'
,job_action => 'BEGIN statspack.purge(i_num_days=>15); END;'
,start_date => SYSDATE +1/24/59 
,repeat_interval => 'FREQ=DAILY; BYHOUR=23; BYMINUTE=00;'
,enabled => TRUE
,comments => 'Statspack purging job');
END;
/

2. Verify the job is created.

set line 200 pages 200
col job_name for a30
col state for a10
SELECT job_name, state FROM dba_scheduler_jobs WHERE job_name='SNAPSHOTPURGE';


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