I needed to setup a small job that would remove partitions from a table with records older than 35 days.
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;
/
