Statspack Purge Job

Adding a job to take hourly statspack snapshots has always been simple e.g.
variable v_jobno number;
begin
dbms_job.submit(:v_jobno,
'statspack.snap(i_snap_level=>7);',
trunc(sysdate+1/24,'HH'),
'trunc(sysdate+1/24,''HH'')',
TRUE);
commit;
end;
/
Purging snapshots automatically used to be a little more difficult, fortunately with more recent versions of statspack you can now do e.g.

variable v_jobno number;
begin
dbms_job.submit(:v_jobno,
'statspack.purge(i_num_days=>;14,i_extended_purge=>;TRUE);',
trunc(sysdate+1),
'trunc(sysdate+1)',
TRUE);
commit;
end;
/
This creates a dbms_job that runs every day at midnight purging any snapshots older than 14 days.

Advertisements