Oracle 依用戶重建索引自動化腳本：碎片診斷、空間回收與定時排程實戰

背景：大量 DML 後的索引碎片與空間浪費

頻繁修改/刪除操作導致索引結構碎片化：已刪除記錄佔用的區塊未釋放，查詢效能下降、表空間膨脹。定期重建索引可同時解決效能恢復與空間回收。

一、動態生成重建腳本 (按 Schema)

SET ECHO OFF FEEDBACK OFF VERIFY OFF PAGESIZE 0 TERMOUT ON HEADING OFF
ACCEPT username CHAR PROMPT 'Enter index owner: '
SPOOL rebuild_&username..sql

SELECT 'REM +-----------------------------------------------+' || CHR(10) ||
       'REM | INDEX NAME : ' || owner || '.' || segment_name ||
       LPAD('|', 33 - LENGTH(owner) - LENGTH(segment_name)) || CHR(10) ||
       'REM | BYTES      : ' || bytes ||
       LPAD('|', 34 - LENGTH(bytes)) || CHR(10) ||
       'REM | EXTENTS    : ' || extents ||
       LPAD('|', 34 - LENGTH(extents)) || CHR(10) ||
       'REM +-----------------------------------------------+' || CHR(10) ||
       'ALTER INDEX ' || owner || '.' || segment_name || CHR(10) ||
       'REBUILD' || CHR(10) ||
       'TABLESPACE ' || tablespace_name || CHR(10) ||
       'STORAGE (' || CHR(10) ||
       '  INITIAL ' || initial_extent || CHR(10) ||
       '  NEXT ' || next_extent || CHR(10) ||
       '  MINEXTENTS ' || min_extents || CHR(10) ||
       '  MAXEXTENTS ' || max_extents || CHR(10) ||
       '  PCTINCREASE ' || pct_increase || CHR(10) ||
       ');' || CHR(10) || CHR(10)
FROM dba_segments
WHERE segment_type = 'INDEX'
  AND owner = '&username'
ORDER BY owner, bytes DESC;

SPOOL OFF

Windows 路徑調整：SPOOL c:\oracle\rebuild_&username..sql

可選過濾：僅重建大於閾值索引 → AND bytes > &max_bytes

二、碎片嚴重度診斷：驗證是否真需重建

ANALYZE INDEX &index_name VALIDATE STRUCTURE;

COL name FORMAT A30
COL del_lf_rows HEADING 'Deleted|Leaf Rows' FORMAT 99999999
COL lf_rows_used HEADING 'Used|Leaf Rows' FORMAT 99999999
COL ratio HEADING '% Deleted|Leaf Rows' FORMAT 999.99999

SELECT name,
       del_lf_rows,
       lf_rows - del_lf_rows AS lf_rows_used,
       TO_CHAR(100 * del_lf_rows / lf_rows, '999.99999') AS ratio
FROM index_stats
WHERE name = UPPER('&index_name');

判斷標準：ratio > 15-20% → 必須重建；5-15% → 觀察趨勢；<5% → 暫緩。

三、生產環境落地最佳實踐

1. 人工審核：生成腳本後，依診斷結果刪除無需重建索引行。
2. 排程執行：納入 DBMS_SCHEDULER 或作業系統 Cron，月度/雙月度低峰期 (如週六 02:00) 執行。
3. 異常處理：遇 ORA-00054 (資源忙/鎖衝突) 記錄跳過，下次輪迴重試。
4. 高頻熱點表排除：核心交易表不可停機重建，改用 ALTER INDEX ... REBUILD ONLINE (Enterprise Edition) 或分區索引逐分區維護。
5. 存儲參數優化：重建時將 PCTINCREASE 設為 0，採用統一 NEXT 避免 Extent 指數增長碎片化。

四、進階替代方案 (11g+)

• Index Coalesce：ALTER INDEX idx COALESCE，合併相鄰葉區塊，無鎖、輕量級，適用輕度碎片。
• Segment Advisor：OEM/DBMS_ADVISOR 自動識別受益重建/收縮的段。
• Online Rebuild：零停機重建，配合 DBMS_REDEFINITION 實現表級線上重組。