Oracle 10g ASSM 自動段空間管理與 SHRINK SPACE 實戰詳解

前言

Oracle 10g 引入 Automatic Segment Space Management (ASSM) 並支援 `ALTER TABLE ... SHRINK SPACE` 指令，可在線降低 High Water Mark (HWM) 回收碎片空間，無需傳統 `MOVE` 操作的鎖表停機。本文透過實測案例，詳細展示 ASSM 環境下 SHRINK 的操作步驟、空間回收效果、RowID 變化機制、索引維護行為、鎖模式與 Redo 產生量對比。

一、 實驗環境與前置檢查

-- 確認表空間為 ASSM (SEGMENT_SPACE_MANAGEMENT = AUTO)
SELECT tablespace_name, block_size, extent_management, allocation_type, segment_space_management
FROM dba_tablespaces WHERE tablespace_name = 'ASSM';

-- 結果：ASSM  8192  LOCAL  UNIFORM  AUTO

關鍵限制：`SHRINK SPACE` **僅支援 ASSM 表空間**，字典管理或手動段空間管理表空間執行將報 `ORA-10635`。

二、 碎片製造與空間狀況分析

-- 建立測試表
CREATE TABLE my_objects TABLESPACE assm AS SELECT * FROM all_objects;  -- 47,828 筆

-- 製造碎片：刪除特定條件資料
DELETE FROM my_objects WHERE object_name LIKE '%C%';  -- 16,950 筆
DELETE FROM my_objects WHERE object_name LIKE '%U%';  -- 4,503 筆
DELETE FROM my_objects WHERE object_name LIKE '%A%';  -- 6,739 筆
COMMIT;

使用 `DBMS_SPACE` 套件檢視空間分佈

-- 傳統檢視 (非 ASSM 專用)
EXEC show_space('MY_OBJECTS', 'DLINGER');
-- Total Blocks: 680, Unused Blocks: 1

-- ASSM 專用檢視
EXEC show_space_assm('MY_OBJECTS', 'DLINGER');
/*
free space 0-25%   : 0
free space 25-50%  : 205
free space 50-75%  : 180
free space 75-100% : 229
Full Blocks        : 45
*/

分析：HWM 下 679 個區塊，僅 45 個滿載，超過 600 個區塊閒置率 25%~100%，嚴重碎片化，適合 SHRINK。

三、 執行 SHRINK SPACE 完整流程

-- 1. 必須先啟用列移動 (Row Movement)
ALTER TABLE my_objects ENABLE ROW MOVEMENT;

-- 2. 執行收縮 (線上操作，無排他鎖)
ALTER TABLE my_objects SHRINK SPACE;

-- 3. 同步收縮索引 (CASCADE)
ALTER TABLE my_objects SHRINK SPACE CASCADE;

四、 收縮效果驗證

EXEC show_space('MY_OBJECTS', 'DLINGER');
/*
Total Blocks: 265 (從 680 降至 265，釋放 61% 空間)
Unused Blocks: 2
*/

EXEC show_space_assm('MY_OBJECTS', 'DLINGER');
/*
free space 25-50% : 1
free space 50-75% : 0
free space 75-100%: 0
Full Blocks       : 249 (絕大多數區塊變滿載)
*/

HWM 從 679 降至 264，空間利用率大幅提升。

五、 核心機制深度剖析：SHRINK vs MOVE

建立小表 `TEST_HWM` (10 筆資料，分佈 4 個連續區塊)，刪除部分資料後觀察 RowID 變化：

SHRINK 演算法邏輯：

1. 從 Segment **最後一列** 開始向前掃描。
2. 從 Segment **最早使用的區塊** 開始向後尋找可用空間。
3. 將尾部列移動至頭部空洞，更新 RowID (含 Index Entry)。
4. 重複直到 HWM 以下無可移動列或無空洞。
5. 最終將 HWM 降至最後一個有資料的區塊邊界。

優點：不需額外臨時空間 (MOVE 需 2 倍空間)，線上執行僅持有 Row-X (SX) 鎖，允許並發 DML。

六、 關鍵議題實測

1. 索引狀態：SHRINK 後 Index 保持 VALID

CREATE INDEX i_my_objects ON my_objects (object_id);
-- 刪除資料、SHRINK 後
SELECT index_name, status FROM user_indexes WHERE index_name = 'I_MY_OBJECTS';
-- 結果：VALID

Oracle 在移動列時同步維護索引 Entry，免去 `MOVE` 後必須 `REBUILD INDEX` 的麻煩。但索引數量多時，維護成本會增加 SHRINK 耗時。

2. 鎖模式：Row-X (SX) Lock，允許並發 DML

SELECT object_id, session_id, oracle_username, locked_mode
FROM v$locked_objects WHERE object_id = (SELECT object_id FROM user_objects WHERE object_name='MY_OBJECTS');
-- LOCKED_MODE = 3 (Row-X / SX)

SX 鎖允許其他 Session 對表進行 `INSERT/UPDATE/DELETE`，僅阻擋 DDL。

3. Redo 產生量：SHRINK 遠大於 MOVE

-- 相同資料量/碎片度測試
-- SHRINK 產生 Redo: ~4.5 MB
-- MOVE  產生 Redo: ~95 KB

原因：SHRINK 逐列移動、更新索引、修改 Bitmap，每步都產生 Redo；MOVE 以區塊為單位批次複製，Redo 精簡。大表 SHRINK 時需關注 Redo Log 空間與 Archive Log 傳輸壓力。

4. 空間需求：SHRINK 不需額外空間

因在原 Segment 內部重組，不像 MOVE 需分配新 Extent。適合磁碟空間緊張環境。

七、 總結與建議

• 適用場景：ASSM 表空間、大量刪除/更新導致 HWM 高懸、需線上回收空間、磁碟空間有限。
• 前置條件：`ENABLE ROW MOVEMENT`、無 Function-based Index (不支援)、無 Materialized View Log。
• 操作建議：
  • 非尖峰時段執行 (Redo 激增)。
  • 大表分批處理或搭配 `SHRINK SPACE COMPACT` (僅重組不降 HWM，後續再 `SHRINK SPACE` 降 HWM，縮短鎖定時間)。
  • 監控 `V$SESSION_LONGOPS` 追蹤進度。