rCleanUpOutOfDateRows

CREATE TABLE BIG_TABLE  AS SELECT level id, SYSDATE OperationDate FROM dual CONNECT BY level < 10000;
--DROP TABLE BIG_TABLE;
SELECT * FROM  BIG_TABLE;

DECLARE
PROCEDURE rCleanUpOutOfDateRows
IS
  -- 1.4M rows got deleted in 20 minutes
  -- Unfortunatelly the are triggers on BIG_TABLE.
  -- So we disable them for the time of delete wchis is safe in our case.
  ci_ChunkSize CONSTANT PLS_INTEGER := 100000;
  TYPE RTable IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
  CURSOR vc_RowsToDelete IS
  SELECT Rowid
    FROM BIG_TABLE cr
   WHERE cr.OperationDate < SYSDATE + 7;
  vt_Rowids RTable;
  vb_Enabled BOOLEAN := TRUE;
BEGIN
  OPEN vc_RowsToDelete;
  LOOP
    FETCH vc_RowsToDelete
     BULK COLLECT
     INTO vt_Rowids
    LIMIT ci_ChunkSize;

    IF vt_Rowids.FIRST IS NOT NULL THEN
      IF vb_Enabled THEN
        -- Enable/Disable triggers only when there are rows to delete
        -- At the end triggers are enabled, we expect that all triggers are enabled
        -- If any trigger was disabled then this procedure enables it.
        EXECUTE IMMEDIATE 'ALTER TABLE BIG_TABLE DISABLE ALL TRIGGERS';
        vb_Enabled := FALSE;
      END IF;

      FORALL vi_Idx IN vt_Rowids.FIRST .. vt_Rowids.LAST
        DELETE BIG_TABLE cr
         WHERE Rowid = vt_Rowids(vi_Idx);
      COMMIT;
    END IF;

    EXIT WHEN vt_Rowids.COUNT < ci_ChunkSize;

  END LOOP;

  CLOSE vc_RowsToDelete;
  IF NOT vb_Enabled THEN
    EXECUTE IMMEDIATE 'ALTER TABLE BIG_TABLE ENABLE ALL TRIGGERS';
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    IF vc_RowsToDelete%ISOPEN THEN
      CLOSE vc_RowsToDelete;
    END IF;
    IF NOT vb_Enabled THEN
      EXECUTE IMMEDIATE 'ALTER TABLE BIG_TABLE ENABLE ALL TRIGGERS';
    END IF;
    RAISE;
END rCleanUpOutOfDateRows;
BEGIN
  rCleanUpOutOfDateRows;
END;