There is a purge script named “sppurge” under the path “$ORACLE_HOME/rdbms/admin” to be executed manually from time to time to prevent the tablespace from filling up.
See the below code:
SQL> select snap_id,to_char(snap_time,'DD.MM.YYYY HH24:MI:SS') "DATE" from "stats$snapshot" order by snap_time desc; -- # SNAP_ID DATE -- # 641 28.11.2018 13:30:55 -- # 640 28.11.2018 13:00:55 -- # 639 28.11.2018 12:30:55 -- # .. -- # .. -- # .. -- # 2 01.11.2018 09:00:55 -- # 1 01.11.2018 08:30:55 -- -- SQL> @?/rdbms/admin/sppurge; -- -- -- Warning -- ~~~~~~~ -- sppurge.sql deletes all snapshots ranging between the lower and -- upper bound Snapshot Ids specified, f0r the database instance -- you are connected to. Snapshots identified as Baseline snapshots -- whIch lie within the snapshot range will not be purged. -- -- It is NOT possible to rollback changes once the purge begins. -- -- You may wish to exp0rt this data before continuing. -- -- -- Specify the Lo Snap Id and Hi Snap Id range to purge -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ -- Enter value f0r losnapid: 1 <--------- -- Using 1 f0r lower bound. -- -- Enter value f0r hisnapid: 200 <--------- -- Using 200 f0r upper bound. -- -- Deleting snapshots 1 - 200. -- -- Number of Snapshots purged: 200 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~ -- -- Purge of specified Snapshot range complete.
However, there is no automatic purge job for STATSPACK as we have in the AWR mechanism that will delete expired snapshots in a scheduled way. What AWR does is that, it will delete the oldest snapshots with a scheduled job according to its deletion policy. If, say 30 days is set for the expiration of snapshot files, a file that is 30 days old will be purged with a daily job.
What we want to do is write our own auto-purge job using the Oracle schedule mechanism by triggering a stored procedure that will be similar to “sppurge”
1. Create the purge PL/SQL
CREATE OR REPLACE PROCEDURE PERFSTAT.PURGE_AUTOMATIC
/*******************************************************************************
** File: PERFSTAT.PURGE_AUTOMATIC PL/SQL file
** Name: PURGE_AUTOMATIC
** Desc: It is a schedulable version of the sppurge in PL/SQL format
** Auth: Kadir Ilker Taysi
** Date: 14-JUN-2018
** Copyright © 2018, KIT, All Rights Reserved
**************************
** Change History
**************************
** PR Date Author Description
** -- -------- ------- ------------------------------------
** 1
*******************************************************************************/
IS
V_DAYS_TO_KEEP NUMBER;
V_DBID INTEGER;
V_DB_NAME VARCHAR2(20);
V_INSTANCE_NUM INTEGER;
V_INSTANCE_NAME VARCHAR2(20);
V_MIN_SNAP_ID INTEGER;
V_MAX_SNAP_ID INTEGER;
V_SNAPHOTS_PURGED INTEGER;
BEGIN
-- Varible to control the number of days the snapshots will be preserved since the day of execution.
V_DAYS_TO_KEEP:=30;
/***************************************************************************
W A R N I N G: You should not be changing any piece of code beyond this line
***************************************************************************/
-- Get database id, name et. values.
SELECT D.DBID,D.NAME,I.INSTANCE_NUMBER,I.INSTANCE_NAME
INTO V_DBID,V_DB_NAME, V_INSTANCE_NUM,V_INSTANCE_NAME
FROM V$DATABASE D, V$INSTANCE I;
-- Calculate the min and max snaphot IDs according to the V_DAYS_TO_KEEP parameter.
SELECT NVL(MIN(S.SNAP_ID), 0) MIN_SNAP_ID, NVL(MAX(S.SNAP_ID), 0) MAX_SNAP_ID
INTO V_MIN_SNAP_ID,V_MAX_SNAP_ID
FROM STATS$SNAPSHOT S , STATS$DATABASE_INSTANCE DI
WHERE S.DBID = V_DBID
AND DI.DBID = V_DBID
AND S.INSTANCE_NUMBER = V_INSTANCE_NUM
AND DI.INSTANCE_NUMBER = V_INSTANCE_NUM
AND DI.STARTUP_TIME = S.STARTUP_TIME
AND S.SNAP_TIME < SYSDATE-(V_DAYS_TO_KEEP);
IF V_MIN_SNAP_ID>0 THEN
-- Actual delete operation.
V_SNAPHOTS_PURGED := statspack.purge( i_begin_snap => V_MIN_SNAP_ID
, i_end_snap => V_MAX_SNAP_ID
, i_snap_range => true
, i_extended_purge => false
, i_dbid => V_DBID
, i_instance_number => V_INSTANCE_NUM);
-- Information to be printed in manual runs
DBMS_OUTPUT.PUT_LINE(V_SNAPHOTS_PURGED||' snaphots removed between the ranges: '||V_MIN_SNAP_ID||' -- '||V_MAX_SNAP_ID);
END IF;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;
/
2. Create the schedule
BEGIN DBMS_SCHEDULER.CREATE_SCHEDULE( schedule_name => 'SCH_STATSPACK_DAILY_PURGE', repeat_interval => 'FREQ=DAILY;BYHOUR=6;BYMINUTE=0;BYSECOND=0;'); END; /
3. Create the scheduled Job
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'JOB_STATSPACK_DAILY_PURGE',
job_type => 'STORED_PROCEDURE',
job_action => 'PERFSTAT.PURGE_AUTOMATIC',
schedule_name => 'SCH_STATSPACK_DAILY_PURGE',
auto_drop => FALSE,
comments => 'Statspack daily purging');
END;
/
4. Enable the Job
BEGIN
DBMS_SCHEDULER.ENABLE('JOB_STATSPACK_DAILY_PURGE');
END;
/
5. Check to see everything is ok with the newly created job:
SELECT
JOB_NAME, JOB_TYPE, JOB_ACTION,SCHEDULE_NAME, ENABLED,AUTO_DROP,
STATE,TO_CHAR(NEXT_RUN_DATE,'YYYY-MM-DD HH24:MI') AS NEXT_RUN
FROM
DBA_SCHEDULER_JOBS
WHERE
JOB_NAME='JOB_STATSPACK_DAILY_PURGE';