Oracle 表與索引可回收空間檢測與 SHRINK 實戰指南

表空間碎片檢測

1. 統計信息更新

ANALYZE TABLE table_name COMPUTE STATISTICS;
-- 或 DBMS_STATS (推薦)
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA', 'TABLE_NAME', CASCADE => TRUE);

2. 高水位 (HWM) 以下可回收空間估算

SELECT table_name,
       (blocks * 8192 / 1024 / 1024) - (num_rows * avg_row_len / 1024 / 1024) AS "Reclaimable_MB"
FROM dba_tables
WHERE owner = UPPER('&owner')
ORDER BY 2 DESC;

原理：已分配空間 - 理論數據空間 = 碎片空間 (近似值)

3. 實際已用塊數驗證

ANALYZE TABLE big_emp1 ESTIMATE STATISTICS;
SELECT table_name, num_rows, blocks, empty_blocks FROM user_tables WHERE table_name='BIG_EMP1';

SELECT COUNT(DISTINCT
       DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) || DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) AS "Used_Blocks"
FROM big_emp1;

SELECT segment_name, segment_type, blocks FROM dba_segments WHERE segment_name='BIG_EMP1';

索引碎片檢測

ANALYZE INDEX index_name VALIDATE STRUCTURE;

COLUMN name FORMAT A15
COLUMN blocks HEADING "ALLOC|BLOCKS"
COLUMN lf_blks HEADING "LEAF|BLOCKS"
COLUMN br_blks HEADING "BRANCH|BLOCKS"
COLUMN empty HEADING "UNUSED|BLOCKS"
SELECT name, blocks, lf_blks, br_blks, blocks-(lf_blks+br_blks) empty FROM index_stats;

-- 或直接查看空間利用率
SELECT name, btree_space, used_space, pct_used FROM index_stats;

pct_used < 50% 通常值得重建或 Shrink。

空間回收方案 (需 COMPATIBLE ≥ 10.0)

驗證腳本 (DBMS_SPACE)

SET SERVEROUTPUT ON
DECLARE
  v_unf_blks NUMBER; v_unf_bytes NUMBER;
  v_fs1_blks NUMBER; v_fs1_bytes NUMBER;
  v_fs2_blks NUMBER; v_fs2_bytes NUMBER;
  v_fs3_blks NUMBER; v_fs3_bytes NUMBER;
  v_fs4_blks NUMBER; v_fs4_bytes NUMBER;
  v_full_blks NUMBER; v_full_bytes NUMBER;
BEGIN
  DBMS_SPACE.SPACE_USAGE('SCHEMA', 'TABLE_NAME', 'TABLE',
    v_unf_blks, v_unf_bytes, v_fs1_blks, v_fs1_bytes,
    v_fs2_blks, v_fs2_bytes, v_fs3_blks, v_fs3_bytes,
    v_fs4_blks, v_fs4_bytes, v_full_blks, v_full_bytes);
  DBMS_OUTPUT.PUT_LINE('Unformatted Blocks = '||v_unf_blks);
  DBMS_OUTPUT.PUT_LINE('FS1 (0-25%) Blocks = '||v_fs1_blks);
  DBMS_OUTPUT.PUT_LINE('FS2 (25-50%) Blocks = '||v_fs2_blks);
  DBMS_OUTPUT.PUT_LINE('FS3 (50-75%) Blocks = '||v_fs3_blks);
  DBMS_OUTPUT.PUT_LINE('FS4 (75-100%) Blocks = '||v_fs4_blks);
  DBMS_OUTPUT.PUT_LINE('Full Blocks = '||v_full_blks);
END;
/
-- 解讀：FS1/FS2 佔比高說明碎片多，適合 SHRINK

操作窗口建議

• 在業務低峰期執行，SHRINK 需排他鎖 (COMPACT 除外)。
• 大表分批處理：SHRINK SPACE COMPACT 可在線運行，後續維護窗口再 SHRINK SPACE。
• 配合 ALTER INDEX ... REBUILD ONLINE 徹底整理索引結構。