Oracle 大量資料刪除最佳實務：分批提交避免鎖爭用與 UNDO 爆滿

問題背景

單次 DELETE 數百萬列會導致：長時間鎖定表、UNDO 表空間暴增、Redo Log 暴增、可能觸發 Snapshot Too Old 錯誤。

解決方案：PL/SQL 分批迴圈刪除

CREATE OR REPLACE PROCEDURE delete_in_batches(
  p_table_name  IN VARCHAR2,
  p_condition   IN VARCHAR2,
  p_batch_size  IN NUMBER DEFAULT 10000
) AS
  v_deleted  NUMBER := 0;
  v_sql      VARCHAR2(4000);
BEGIN
  v_sql := 'DELETE FROM ' || p_table_name || 
           ' WHERE ' || p_condition || 
           ' AND ROWNUM <= :batch_size';

  LOOP
    EXECUTE IMMEDIATE v_sql USING p_batch_size;
    v_deleted := v_deleted + SQL%ROWCOUNT;
    EXIT WHEN SQL%ROWCOUNT = 0;
    COMMIT;  -- 每批提交，釋放鎖與 UNDO
  END LOOP;

  COMMIT;
  DBMS_OUTPUT.PUT_LINE('完成：共刪除 ' || v_deleted || ' 筆資料');
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END;
/

呼叫範例

SET TIMING ON
EXEC delete_in_batches('HS_DLF_DOWNLOG_HISTORY', 'NUMDLFLOGGUID < 11100000', 10000);

關鍵優化細節

• ROWNUM 限制批次大小： 避免單次交易過大
• 每批 COMMIT： 釋放 Row Lock、UNDO 空間、Redo Log 緩衝
• 使用動態 SQL： 通用於任意表與條件
• 自主交易： 若需在主交易外獨立提交，可加 PRAGMA AUTONOMOUS_TRANSACTION;

進階替代方案

效能調校建議

• 批次大小 5,000~50,000 視表寬度與索引數調整
• 確認刪除條件欄位有索引，避免 Full Table Scan
• 監控 V$SESSION_LONGOPS 追蹤進度
• 離峰時段執行，降低對線上業務影響

1. 上一篇文章： Oracle 資料表分區策略實務：從表篩選到分區鍵設計
2. 下一篇文章： Oracle PL/SQL 實戰案例：員工薪資調整與異動追蹤

Oracle 資料庫相關知識

SQL 嵌套查詢（子查詢）完整教程

SQL 嵌套查詢（子查詢）語法與實戰詳解 SELECT 語句

Win7 徹底解除安裝 Oracle 11g 圖文步驟：服務、註冊表、環境變數、目錄全清理

⚠️ 操作前請備份重要資料，確認無其他 Oracle 實例共

Oracle 10g Server 安裝預備步驟與完整圖文教學：高級安裝、Loopback Adapter、資料庫建立

下載與空間需求 OTN 下載：Oracle 10g 下載頁

Oracle 資料區塊內部結構深度解讀

Data Block (資料區塊) 是 Oracle 儲存管