Oracle DELETE 後表空間不釋放問題：ANALYZE、EXP/IMP、SHRINK SPACE 三大解法實測

現象與根因

大量 `DELETE` 後，表 **High Water Mark (HWM) 不下降**，全表掃描仍讀取空塊，查詢效能不升反降。原因：**DELETE 僅標記行刪除、釋放塊內空間，不移動 HWM、不歸還Extent 給表空間**。

三大解法對比

實戰驗證腳本

-- 1. 建測試表、塞 100 萬行、刪 90%
CREATE TABLE t_shrink AS SELECT * FROM dba_objects WHERE rownum <= 1000000;
DELETE FROM t_shrink WHERE rownum <= 900000;
COMMIT;

-- 2. 觀察 HWM 與空間
SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name='T_SHRINK';
-- blocks 仍大，empty_blocks 增多

-- 3. 方案 A：統計資訊 (不降 HWM)
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'T_SHRINK');
-- 查詢計畫可能改善，但 blocks 不變

-- 4. 方案 C：SHRINK SPACE (推薦)
ALTER TABLE t_shrink ENABLE ROW MOVEMENT;
ALTER TABLE t_shrink SHRINK SPACE;  -- 或 SHRINK SPACE COMPACT 僅整理不調整 HWM
ALTER TABLE t_shrink DISABLE ROW MOVEMENT;

-- 5. 再次觀察
SELECT blocks, empty_blocks FROM user_tables WHERE table_name='T_SHRINK';
-- blocks 大幅下降，空間歸還表空間

SHRINK SPACE 關鍵細節

• COMPACT 選項：僅整理塊內空間、不調整 HWM、不釋放 Extent，**不需獨佔鎖**，適合高併發窗口先行整理，維護窗口再執行完整 `SHRINK SPACE`。
• 索引同步收縮：`ALTER INDEX idx SHRINK SPACE` 同理可用。
• LOB 欄位：`ALTER TABLE t MODIFY LOB (col) (SHRINK SPACE)`。
• 前置條件：表必須在 **本地管理表空間 (LMT)** 且啟用 `ROW MOVEMENT`；IOT、Cluster Table、含 LONG/Cluster Key 不支援。

避坑指南

1. **不要以為 ANALYZE 能釋放空間**——它只更新統計資訊，HWM 原地不動。
2. **大表 SHRINK 分批處理**：配合 `DBMS_PARALLEL_EXECUTE` 按 Rowid 範圍分塊，避免單次長事務撐爆 Undo/Redo。
3. **備份策略配合**：Shrink 後立即執行增量備份，避免恢復點目標 (RPO) 缺口。
4. **監控長事務**：`V$SESSION_LONGOPS` 追蹤 Shrink 進度，預估完成時間。

結語

**`SHRINK SPACE` 是 10g+ 版本解決 DELETE 後空間回收的標準答案**：在線、保留物件屬性、真正降 HWM 歸還空間。配合 `COMPACT` 預整理 + 維護窗口正式收縮，可在零停機業務高峰期安全執行。舊版本 9i 僅能走 EXP/DROP/IMP 路徑，需安排停機窗口。