To automatically purge STATSPACK snaphosts.

With STATSPACK.PURGE, you can delete all snapshots that are older than a certain number of days.

SQL> EXEC STATSPACK.PURGE(DAYS);

E.g., to purge all snapshots older than 14 days

SQL> EXEC STATSPACK.PURGE(14);

To automatically purge STATSPACK snapshots, create a job using the DBMS_JOB procedure which runs regularly. A job has to be created by the owner of the schema where statspack was installed.

E.g., to create a job that runs every day at midnight to purge any STATSPACK snapshots older than 14 days

-- The STATSPACK owner

SQL> SHOW USER
USER is "PERFSTAT"

-- Create a job

SET SERVEROUTPUT ON
VARIABLE JOB_NUMBER NUMBER;

BEGIN
  DBMS_JOB.SUBMIT(JOB  => :JOB_NUMBER,
                  WHAT => 'STATSPACK.PURGE(I_NUM_DAYS => 14, I_EXTENDED_PURGE => TRUE);',
                  NEXT_DATE => TRUNC(SYSDATE) +1,     -- Executes the job at the midnight
                  INTERVAL  => 'TRUNC(SYSDATE) +1');  -- Executes the job again on the next day at the midnight
  COMMIT;
END;
/

To get job details

SET PAGES 999
SET LINES 300
COL JOB FOR 9999999
COL WHAT FOR A25
COL SCHEMA_USER FOR A15 
COL NEXT_RUN_DATE FOR A20 
COL INTERVAL FOR A18
COL FAILURES FOR 999999999

SELECT JOB,
       SCHEMA_USER,
       WHAT,
       TO_CHAR(NEXT_DATE,'DD-MON-YYYY HH24:MI:SS') NEXT_RUN_DATE,
       INTERVAL,
       FAILURES
FROM
       DBA_JOBS
WHERE
       WHAT LIKE 'STATSPACK.PURGE%'; 

     JOB SCHEMA_USER     WHAT                      NEXT_RUN_DATE        INTERVAL               FAILURES
-------- --------------- ------------------------- -------------------- ------------------ ------------
     907 PERFSTAT        STATSPACK.PURGE(I_NUM_DAY 09-OCT-2020 00:00:00 TRUNC(SYSDATE) +1
                         S => 14, I_EXTENDED_PURGE
                          => TRUE);


SQL> SELECT SYSTIMESTAMP FROM DUAL;

SYSTIMESTAMP
---------------------------------------------------------------------------
08-OCT-20 05.08.23.293604 PM +03:00

In this demo, the purge job was created with ID of 907. The job will run at the next day as 00:00:00.

If you wish to run the purge job now

-- EXEC DBMS_JOB.RUN(907);

If you wish to reschedule the purge job, for instance to run every 12 hours

-- EXEC DBMS_JOB.INTERVAL(907, 'sysdate+12/24');

If you wish to remove the purge job

-- EXEC DBMS_JOB.REMOVE(907);

Statistics Package (STATSPACK) Guide (Doc ID 394937.1)
FAQ- Statspack Complete Reference (Doc ID 94224.1)

Written At

08 OCT 202017:00

Red Hat Release

7.x x64

Database Release

11.2.0.4