procedure to remove old partition from the table.

procedure to remove old partition from the table.

Assuming that the table is range partitioned based on date column.


CREATE OR REPLACE PROCEDURE P1MONI.remove_ndays_old_partitions (days IN NUMBER,tab_name IN VARCHAR2)
     IS
       CURSOR V_CUR
         IS
           SELECT  PARTITION_NAME, HIGH_VALUE
                   FROM USER_TAB_PARTITIONS
            WHERE TABLE_NAME = upper(tab_name) AND INTERVAL = 'YES';
--WHERE TABLE_NAME = upper(tab_name);

      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;
          --DBMS_OUTPUT.PUT_LINE('high value - '|| V_HIGH_VALUE);

IF V_HIGH_VALUE <= TRUNC(SYSDATE -days,'DD')
            THEN
        --DBMS_OUTPUT.PUT_LINE('ALTER TABLE '|| tab_name||' DROP PARTITION ' || V_REC.PARTITION_NAME || ';');
        EXECUTE IMMEDIATE 'ALTER TABLE '|| tab_name||' DROP PARTITION ' || V_REC.PARTITION_NAME ||' UPDATE GLOBAL INDEXES';
          END IF;
        END LOOP;
END;
/