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`
只重大索引:`WHERE ... AND bytes > &max_bytes`
SQL> ANALYZE INDEX idx_name VALIDATE STRUCTURE;
COL name HEADING 'Index 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(del_lf_rows / lf_rows * 100, '999.99999') AS ratio
FROM index_stats
WHERE name = UPPER('&index_name');
判準:`ratio > 15~20%` → **必須重建**。
「產生腳本 → 分析篩選 → 手動微調 → 排程自動化」四步驟,將索引重建從人工苦力變成可控自動化作業。關鍵在於**事前分析碎片度**,避免無效重建浪費資源與鎖定風險。