How to Efficiently Delete Massive Data Sets with a PL/SQL Procedure

Deleting millions of rows in a single transaction can overload an Oracle database, so the article presents a controlled bulk‑delete method implemented as a PL/SQL stored procedure.

Key operational guidelines

Define a batch size (e.g., 1,000 rows) and optionally partition by ROWID to limit each delete operation.

Run the procedure during low‑traffic periods to reduce contention.

Insert a short pause (e.g., 3 seconds) between batches to give the system time to recover.

Procedure implementation

CREATE PROCEDURE clear_ub(
  v_start NUMBER,
  v_end   NUMBER
) AS
  TYPE cn_type IS TABLE OF VARCHAR2(100);
  l_start  NUMBER;
  cn_tab   cn_type;
  CURSOR cur_data IS
    SELECT /*+ FIRST_ROWS(1000) */ cn
    FROM (
      SELECT ud.cn, ROWNUM RN
      FROM (SELECT cn FROM tt.USER_18MONTHS_20120607_INFO WHERE cn IS NOT NULL) ud
      WHERE ROWNUM < v_end
    )
    WHERE RN >= v_start;
BEGIN
  OPEN cur_data;
  LOOP
    FETCH cur_data BULK COLLECT INTO cn_tab LIMIT 1000;
    EXIT WHEN cn_tab.COUNT = 0;
    DBMS_OUTPUT.PUT_LINE(cn_tab.COUNT || ' rows');
    l_start := DBMS_UTILITY.GET_TIME;
    FORALL i IN cn_tab.FIRST .. cn_tab.LAST
      DELETE FROM tt.user_info WHERE cn = cn_tab(i);
    COMMIT;
    DBMS_LOCK.SLEEP(3);
    DBMS_OUTPUT.PUT_LINE('Delete : ' || (DBMS_UTILITY.GET_TIME - l_start));
  END LOOP;
  CLOSE cur_data;
END;

Explanation of the code

The cursor cur_data selects a range of rows between v_start and v_end. Each iteration fetches up to 1,000 identifiers into the collection cn_tab using BULK COLLECT. The FORALL statement deletes those rows in a single DML operation, followed by a COMMIT. After each batch, DBMS_LOCK.SLEEP(3) pauses execution for three seconds, and timing information is printed with DBMS_OUTPUT to monitor performance.

Result

By processing deletions in manageable chunks and inserting brief pauses, the procedure reduces lock contention, limits undo/redo generation, and keeps the database responsive during large‑scale clean‑up tasks.

Source: ITpub technical community.