Oracle 按用戶重建索引自動化腳本：生成、分析碎片、排除忙表、定時執行全流程

為何重建索引

• 大量 DELETE 後索引塊未釋放，佔用空間、降低掃描效率。
• 重建可回收空間、壓縮高度、改善 Clustering Factor。

一鍵生成重建腳本

SET ECHO OFF FEEDBACK OFF VERIFY OFF PAGESIZE 0 TERMOUT ON HEADING OFF
ACCEPT username CHAR PROMPT 'Enter index owner: '
SPOOL /oracle/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 lf_rows_used,
       TO_CHAR(del_lf_rows / lf_rows * 100, '999.99999') ratio
FROM index_stats
WHERE name = UPPER('&index_name');

判準：ratio > 15~20% 則重建收益大。

生產部署注意事項

1. 生成腳本後人工審核，剔除極忙表索引（避免 ORA-00054 資源忙）。
2. 納入 DBMS_SCHEDULER / Cron，非業務高峰（如凌晨 02:00）執行。
3. 遇 ORA-00054 記錄跳過，下次輪詢自動重試。
4. 重建後 DBMS_STATS.GATHER_INDEX_STATS 同步統計資訊。