Oracle 依使用者重建索引自動化腳本：產生、分析碎片、排程執行全流程

為何需要重建索引？

• 大量 DML (DELETE/UPDATE) 導致 **索引碎片**：葉子塊稀疏、高度增加、Clustering Factor 惡化。
• 重建效果：**釋放已刪除項目空間、降低樹高、重排 Clustering Factor、回收表空間**。
• 表重組需 ALTER TABLE MOVE / SHRINK SPACE / EXPORT/IMPORT，索引重建獨立進行。

第一階段：產生重建腳本 (按 Owner)

SET ECHO OFF FEEDBACK OFF VERIFY OFF PAGESIZE 0 TERMOUT ON HEADING OFF
ACCEPT owner CHAR PROMPT 'Enter index owner: '

SPOOL rebuild_&owner..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 = UPPER('&owner')
ORDER  BY owner, bytes DESC;

SPOOL OFF

Windows 環境修改 SPOOL 路徑：SPOOL C:\oracle\rebuild_&owner..sql。
僅重建大索引：加條件 AND bytes > &max_bytes。

第二階段：碎片分析 — 判斷是否真需重建

-- 1. 驗證結構 (鎖表，較久)
ANALYZE INDEX &index_name VALIDATE STRUCTURE;

-- 2. 查詢碎片指標
COLUMN name FORMAT A30
COLUMN del_lf_rows HEADING 'Deleted|Leaf Rows' FORMAT 999,999,999
COLUMN lf_rows_used HEADING 'Used|Leaf Rows' FORMAT 999,999,999
COLUMN pct_deleted HEADING '% Deleted|Leaf Rows' FORMAT 999.99999

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

重建判準

• 刪除比率 > 15~20% → **必須重建**。
• 高度 (HEIGHT) > 3** 且刪除比率 > 10% → 考慮重建。
• Clustering Factor 接近表行數** → 重建可改善 (但不保證)。
• **LMT + ASSM 環境下**，碎片影響較小，**優先檢查統計資訊是否最新**。

-- 建立 Scheduler Job (每月第一天凌晨 02:00)
BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
        job_name        => 'JOB_REBUILD_INDEXES_APP_OWNER',
        job_type        => 'SCRIPT',
        job_action      => '@/u01/scripts/rebuild_APP_OWNER.sql',  -- 外部腳本檔
        start_date      => TRUNC(SYSDATE) + 2/24,  -- 凌晨 02:00
        repeat_interval => 'FREQ=MONTHLY; BYMONTHDAY=1; BYHOUR=2; BYMINUTE=0; BYSECOND=0',
        enabled         => TRUE,
        comments        => 'Monthly index rebuild for APP_OWNER, skip on ORA-00054'
    );
END;
/

-- 19c 自動索引功能 (需 Enterprise Edition + Tuning Pack)
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE', 'IMPLEMENT');  -- 自動建立/重建/刪除
SELECT * FROM DBA_AUTO_INDEX_CONFIG;
SELECT * FROM DBA_AUTO_INDEX_EXECUTIONS ORDER BY EXECUTION_START DESC;