Oracle 海量數據批量刪除專家方案：分批提交 + 自治事務

核心原則

批量刪除海量數據核心思想：分批刪除、逐次提交，避免巨大事務導致 UNDO 表空間暴增、鎖升級、長時間阻塞。

實戰存儲過程

針對可通過主鍵/條件刪除的表，測試 FOR ALL 無顯著優勢，採用動態 SQL + 自治事務：

CREATE OR REPLACE PROCEDURE delBigTab (
    p_TableName IN VARCHAR2,   -- 表名
    p_Condition IN VARCHAR2,   -- 刪除條件（不含 WHERE）
    p_Count     IN VARCHAR2    -- 每批次行數
) AS
    PRAGMA AUTONOMOUS_TRANSACTION;  -- 關鍵：每批獨立提交
    n_delete NUMBER := 0;
BEGIN
    WHILE TRUE LOOP
        EXECUTE IMMEDIATE
            'DELETE FROM ' || p_TableName ||
            ' WHERE ' || p_Condition ||
            ' AND ROWNUM <= :rn'
        USING p_Count;

        IF SQL%NOTFOUND THEN
            EXIT;
        ELSE
            n_delete := n_delete + SQL%ROWCOUNT;
        END IF;
        COMMIT;  -- 每批提交，釋放 UNDO、鎖
    END LOOP;
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Finished! Totally ' || TO_CHAR(n_delete) || ' records deleted!');
END;

關鍵點解析

• PRAGMA AUTONOMOUS_TRANSACTION：使每次循環成為獨立事務，主事務不受影響，避免「大事務」風險。
• ROWNUM <= :rn：限制單次刪除行數，綁定變量防注入、利於游標共享。
• SQL%ROWCOUNT 累計總刪除量，便於審計。
• 適用場景：有主鍵/唯一索引支持條件過濾的大表；無索引全表刪除建議改用 TRUNCATE 或分區交換。

擴展建議

1. 並行刪除：配合 DBMS_PARALLEL_EXECUTE（11g+）將大表切分為多塊並行處理。
2. 監控進度：將進度寫入日志表，長時間作業可斷點續傳。
3. 高水位線：刪除後考慮 ALTER TABLE ... SHRINK SPACE 釋放空間。