How to create a stored procedure for partition maintenance, executed by the Oracle Scheduler.

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;
/