CREATE OR REPLACE

  PROCEDURE DROP_INTERVAL_PARTITION_SP

    IS

      CURSOR V_CUR

        IS

          SELECT  PARTITION_NAME,

                  HIGH_VALUE

            FROM  USER_TAB_PARTITIONS

            WHERE TABLE_NAME = 'MOBILE_TAP'

              AND INTERVAL = 'YES';

      V_HIGH_VALUE TIMESTAMP;

    BEGIN

        FOR V_REC IN V_CUR LOOP

          EXECUTE IMMEDIATE 'BEGIN :1 := ' || V_REC.HIGH_VALUE || '; END;'

            USING OUT V_HIGH_VALUE;

          IF V_HIGH_VALUE <= TRUNC(SYSDATE,'MM')

            THEN

              DBMS_OUTPUT.PUT_LINE('ALTER TABLE INTERVAL_PART_TEST DROP PARTITION ' || V_REC.PARTITION_NAME || ';');

              EXECUTE IMMEDIATE 'ALTER TABLE INTERVAL_PART_TEST DROP PARTITION ' || V_REC.PARTITION_NAME;

          END IF;

        END LOOP;

END;

/



BEGIN 

    DBMS_SCHEDULER.CREATE_SCHEDULE(

                                   schedule_name   => 'DROP_INTERVAL_PTNTAP_SCHED',

                                   start_date      => TIMESTAMP '2017-05-11 09:35:00.000000 US/EASTERN',

                                   repeat_interval => 'FREQ=MONTHLY; INTERVAL=1'

                                  );

    DBMS_SCHEDULER.CREATE_JOB(

                              job_name      => 'DROP_INTERVAL_PARTITIONTAP_JOB',

                              job_type      => 'STORED_PROCEDURE',

                              job_action    => 'DROP_INTERVAL_PARTITION_SP',

                              schedule_name => 'DROP_INTERVAL_PTNTAP_SCHED',

                              enabled       => TRUE

                             );

    COMMIT; 

END; 

/


EXEC DBMS_SCHEDULER.RUN_JOB('DROP_INTERVAL_PARTITIONTAP_JOB');

SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE FROM ALL_TAB_PARTITIONS WHERE TABLE_NAME ='MOBILE_TAP';