表碎片是導致全表掃描效能下降、高水位線 (HWM) 虛高、備份窗口延長的隱形殺手。本文從數據塊層面剖析碎片產生機制,對比 Shrink Space、Online Redefinition、Partition Exchange、CTAS 等重組方案優劣,提供生產環境零停機重組標準作業流程 (SOP)。
DBA_FREE_SPACE 顯示大量小塊自由空間,最大連續自由區塊遠小於總自由空間。BLOCKS (DBA_TABLES) ≫ EMPTY_BLOCKS + AVG_ROW_LEN * NUM_ROWS / BLOCK_SIZECHAIN_CNT / AVG_SPACE 異常偏高| 方案 | 鎖定模式 | 適用表大小 | 索引/約束/觸發器處理 | 臨時空間 | 版本要求 |
|---|---|---|---|---|---|
| ALTER TABLE ... SHRINK SPACE [COMPACT] + CASCADE | 行級鎖 (短暫排他鎖) | < 100GB | 自動維護 | 極少 | 10g+,需啟用 Row Movement |
| DBMS_REDEFINITION (Online Redefinition) | 僅同步切換瞬間排他鎖 | 任意大小 (TB 級) | 自動同步/手動重建 | 約 1.2x 表大小 | 9i+ (11g+ 支援並行 DML 同步) |
| Partition Exchange + Subpartition | 元數據操作 (毫秒級) | 已分區表 | 需預建目標分區索引 | 無額外 | 8i+ |
| CTAS (CREATE TABLE AS SELECT) + RENAME | 全程排他鎖 | < 50GB | 需手動重建所有依賴物件 | 1x 表大小 | 所有版本 |
| EXP/IMP 或 EXPDP/IMPDP (TRUNCATE+INSERT) | 全程排他鎖 | 中小表 | 參數控制 | 轉儲檔空間 | 所有版本 |
-- 1. 檢查表是否可重定義 (需主鍵或偽主鍵)
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('SCHEMA', 'BIG_TABLE', DBMS_REDEFINITION.CONS_USE_PK);
-- 2. 建立中間表 (相同結構,可調整存儲屬性/分區/壓縮)
CREATE TABLE schema.big_table_int
TABLESPACE data_new
COMPRESS FOR OLTP
AS SELECT * FROM schema.big_table WHERE 1=0;
-- 3. 複製依賴物件:索引、約束、觸發器、權限、統計資訊
-- 使用 DBMS_METADATA.GET_DDL 批量生成 DDL,調整 TABLESPACE 後在中間表創建
-- 4. 啟動重定義 (並行 DML 同步增量變更)
EXEC DBMS_REDEFINITION.START_REDEF_TABLE(
uname => 'SCHEMA',
orig_table => 'BIG_TABLE',
int_table => 'BIG_TABLE_INT',
options_flag => DBMS_REDEFINITION.CONS_USE_PK);
-- 5. 定期同步增量 (業務低峰期執行,縮短最終切換窗口)
EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('SCHEMA', 'BIG_TABLE', 'BIG_TABLE_INT');
-- 6. 完成重定義 (極短鎖定,通常 < 5 秒)
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('SCHEMA', 'BIG_TABLE', 'BIG_TABLE_INT');
-- 7. 驗證並清理
-- 對比行數、約束狀態、索引可用性、統計資訊
DROP TABLE schema.big_table_int PURGE;
DELETE /*+ PARALLEL(8) */ + 定期 SHRINK SPACE COMPACT。SEGMENT_ADVISOR 建議、HWM 增長趨勢、鏈式行比率。