Applications generates temporary data which may need less than minutes to days, these data need to be purged to claim spaces on database and housekeeping. One way to do this is to create a partitioned table and drops the older partitions based on a regular interval.

The below example shows how to do this by dropping the oldest partition by a scheduler jobs.

Step1: Create a stored procedure which will find the oldest partition to be deleted and deletes it.

CREATE OR REPLACE PROCEDURE TEST.SP_PURGE_TABLE$1
IS
pname VARCHAR2 (100);
BEGIN
SELECT partition_name
INTO pname
FROM user_tab_partitions
WHERE table_name = ‘TABLE$1’ AND ROWNUM = 1
ORDER BY partition_position DESC;

EXECUTE IMMEDIATE ‘alter table TABLE$1 drop partition ‘ || pname;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
RAISE;
END SP_PURGE_TABLE$1;
/

Step2: Create a scheduler jobs to execute the stored procedure on a given interval lets say 3 days.

BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB
(
job_name => ‘PURGE_TABLE$1’
,start_date => TO_TIMESTAMP_TZ(‘2020/10/01 05:00:00.000000 +06:00′,’yyyy/mm/dd hh24:mi:ss.ff tzr’)
,repeat_interval => ‘FREQ=DAILY;INTERVAL=3’
,end_date => NULL
,job_class => ‘DEFAULT_JOB_CLASS’
,job_type => ‘STORED_PROCEDURE’
,job_action => ‘TEST.SP_PURGE_TABLE$1’
,comments => NULL
);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => ‘PURGE_TABLE$1’
,attribute => ‘RESTARTABLE’
,value => TRUE);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => ‘PURGE_TABLE$1’
,attribute => ‘LOGGING_LEVEL’
,value => SYS.DBMS_SCHEDULER.LOGGING_OFF);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => ‘PURGE_TABLE$1’
,attribute => ‘MAX_FAILURES’);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => ‘PURGE_TABLE$1’
,attribute => ‘MAX_RUNS’);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => ‘PURGE_TABLE$1’
,attribute => ‘STOP_ON_WINDOW_CLOSE’
,value => FALSE);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => ‘PURGE_TABLE$1’
,attribute => ‘JOB_PRIORITY’
,value => 3);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => ‘PURGE_TABLE$1’
,attribute => ‘SCHEDULE_LIMIT’);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => ‘PURGE_TABLE$1’
,attribute => ‘AUTO_DROP’
,value => FALSE);

SYS.DBMS_SCHEDULER.ENABLE
(name => ‘PURGE_TABLE$1’);
END;
/

The above job will execute every three days and will drop the lowest positioned partition from the table.