The procedure should be flexible enough to accept different tables in different schemas, and there should be some basic logging, too.
Below is how I solved it.
First, grant permission, setup the logging table and add a sequence:
connect / as sysdba
grant select on dba_tab_partitions to scott;
connect scott/tiger
whenever sqlerror continue
drop sequence part_maint_seq;
create sequence part_maint_seq
increment by 1
start with 1
nomaxvalue
nominvalue
nocycle;
drop table partition_maintenance;
create table partition_maintenance(
opid number not null,
table_owner varchar2(8),
table_name varchar2(30),
partition_name varchar2(30),
operation varchar2(10),
success char(1),
exe_date date,
exec_by varchar2(30) default 'automatic',
errormsg varchar2(512)
);
alter table partition_maintenance
add constraint part_maint_pk primary key (opid);
Then, create the stored procedure that will do the job. It requires two parameters to be passed to it: table owner and table name. Note that the procedure will need to be called with invoker's right, since it is accessing DBA_TAB_PARTITION:
connect scott/tiger
set serveroutput on
CREATE OR REPLACE PROCEDURE DROP_PARTITIONS ( towner IN VARCHAR2, tname IN VARCHAR2) AUTHID CURRENT_USER AS
PNAME DBA_TAB_PARTITIONS.PARTITION_NAME%TYPE;
L_DATA LONG;
V_HVD DATE;
V_PURGE_DATE DATE;
SQLTORUN VARCHAR2(200);
CURSOR PART_NAME
IS
SELECT PARTITION_NAME
FROM DBA_TAB_PARTITIONS
WHERE TABLE_NAME = UPPER(tname)
AND TABLE_OWNER = UPPER(towner)
AND INTERVAL='YES'
ORDER BY partition_position;
BEGIN
OPEN PART_NAME;
FETCH PART_NAME INTO PNAME;
IF PART_NAME%ROWCOUNT = 0 THEN
dbms_output.put_line('no such table as ' || UPPER(towner) ||'.'||UPPER(tname) );
INSERT INTO PARTITION_MAINTENANCE (OPID, TABLE_OWNER,TABLE_NAME,PARTITION_NAME, OPERATION, SUCCESS, EXE_DATE, ERRORMSG)
VALUES (PART_MAINT_SEQ.NEXTVAL,UPPER(towner),UPPER(tname),PNAME ,'DROP','N', SYSDATE, 'No table such as '|| towner || '.' || tname );
COMMIT;
END IF;
WHILE PART_NAME%FOUND
LOOP
--DBMS_OUTPUT.PUT_LINE( pname );
SELECT HIGH_VALUE
INTO L_DATA
FROM DBA_TAB_PARTITIONS
WHERE TABLE_OWNER= UPPER(towner)
AND TABLE_NAME = UPPER(tname)
AND PARTITION_NAME = PNAME;
--dbms_output.put_line('partition name: ' || pname || ' ' || trim (substr ( l_data, 12, 10 ) ) );
V_HVD := TO_DATE(TRIM (SUBSTR ( L_DATA, 12, 10 )) ,'YYYY-MM-DD');
-- dbms_output.put_line('high value date: ' || v_hvd);
V_PURGE_DATE := TRUNC(SYSDATE)-35;
--dbms_output.put_line('purge date: ' || v_purge_date);
IF V_HVD < V_PURGE_DATE THEN
DBMS_OUTPUT.PUT_LINE('partition ' || PNAME ||' too old, high value date for partition is: ' || V_HVD);
-- drop statement goes here
SQLTORUN := 'alter table ' || towner || '.' || tname || ' drop partition ' || PNAME || ' UPDATE INDEXES';
DBMS_OUTPUT.PUT_LINE(SQLTORUN);
EXECUTE IMMEDIATE SQLTORUN;
-- logging to a separate table goes here
INSERT INTO PARTITION_MAINTENANCE (OPID, TABLE_OWNER,TABLE_NAME,PARTITION_NAME, OPERATION, SUCCESS, EXE_DATE, ERRORMSG)
VALUES (PART_MAINT_SEQ.NEXTVAL,UPPER(towner),UPPER(tname),PNAME ,'DROP','Y', SYSDATE, 'Partition ' || pname || ' of table ' || towner || '.' || tname || ' was dropped.');
COMMIT;
END IF;
FETCH PART_NAME INTO PNAME;
END LOOP;
CLOSE PART_NAME;
END;
/
Finally, create a program of type "STORED_PROCEDURE", define its arguments, and create a job that executes the program on a regular basis:
whenever sqlerror continue
set serveroutput on
BEGIN
DBMS_SCHEDULER.DROP_JOB(
job_name => 'DROP_PARTITION_5WEEK'
);
DBMS_SCHEDULER.DROP_PROGRAM(
program_name => 'DROP_PARTITION_PROG'
);
DBMS_SCHEDULER.create_program (
program_name => 'DROP_PARTITION_PROG',
program_type => 'STORED_PROCEDURE',
program_action => 'DROP_PARTITIONS',
number_of_arguments => 2,
enabled => FALSE,
comments => 'Program to drop partitions with contents older than 35 days..');
DBMS_SCHEDULER.define_program_argument (
program_name => 'DROP_PARTITION_PROG',
argument_name => 'towner',
argument_position => 1,
argument_type => 'VARCHAR2',
default_value => 'SCOTT');
DBMS_SCHEDULER.define_program_argument (
program_name => 'DROP_PARTITION_PROG',
argument_name => 'tname',
argument_position => 2,
argument_type => 'VARCHAR2',
default_value => 'SALES');
DBMS_SCHEDULER.enable( name => 'DROP_PARTITION_PROG');
DBMS_SCHEDULER.CREATE_JOB
(
job_name => 'DROP_PARTITION_5WEEK',
program_name => 'DROP_PARTITION_PROG',
start_date => TO_TIMESTAMP_TZ('2015/05/26 21:45:00.000000 +02:00','yyyy/mm/dd hh24:mi:ss.ff tzh:tzm'),
repeat_interval => 'FREQ=WEEKLY;',
end_date => NULL,
enabled => TRUE,
comments => 'Job will execute the program drop_partitions.');
END;
/
