一、表碎片空間計算
-- 1. 更新統計資訊
ANALYZE TABLE big_emp1 COMPUTE STATISTICS;
-- 或
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','BIG_EMP1');
-- 2. 計算 HWM 以下實際資料佔用 vs 分配空間
SELECT table_name,
(blocks * 8192 / 1024 / 1024) - (num_rows * avg_row_len / 1024 / 1024) AS "Data_below_HWM_MB"
FROM dba_tables
WHERE owner = UPPER('&OWNER')
ORDER BY 2 DESC;
-- 3. 實際使用區塊數 (去重 ROWID 區塊)
SELECT COUNT(DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)||DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) AS "Used_Blocks"
FROM big_emp1;
-- 4. 段分配區塊數
SELECT segment_name, segment_type, blocks FROM dba_segments WHERE segment_name='BIG_EMP1';
二、索引碎片檢測
-- 1. 驗證索引結構 (產生 INDEX_STATS)
ANALYZE INDEX idx_big_emp1 VALIDATE STRUCTURE;
-- 2. 查看 INDEX_STATS 視圖
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;
-- 3. 空間利用率快速檢視
SELECT name, btree_space, used_space, pct_used FROM index_stats;
-- pct_used 低於 50% 考慮 REBUILD/SHRINK
三、SHRINK SPACE 回收空間完整語法 (需 COMPATIBLE >= 10.0)
-- 1. 啟用 ROW MOVEMENT (必須)
ALTER TABLE scott.emp ENABLE ROW MOVEMENT;
-- 2. 僅壓縮空間,不移動 HWM (線上、快速)
ALTER TABLE scott.emp SHRINK SPACE COMPACT;
-- 3. 壓縮空間並降低 HWM (會鎖表、產生大量 REDO)
ALTER TABLE scott.emp SHRINK SPACE;
-- 4. 連同依賴索引一併壓縮
ALTER TABLE scott.emp SHRINK SPACE CASCADE;
-- 5. 僅壓縮索引
ALTER INDEX idx_emp_name SHRINK SPACE;
四、DBMS_SPACE 驗證回收效果
SET SERVEROUTPUT ON
DECLARE
v_unf_b NUMBER; v_unf_by NUMBER;
v_fs1_b NUMBER; v_fs1_by NUMBER;
v_fs2_b NUMBER; v_fs2_by NUMBER;
v_fs3_b NUMBER; v_fs3_by NUMBER;
v_fs4_b NUMBER; v_fs4_by NUMBER;
v_full_b NUMBER; v_full_by NUMBER;
BEGIN
DBMS_SPACE.SPACE_USAGE('SYSTEM','T_SHRINK','TABLE',
v_unf_b, v_unf_by, v_fs1_b, v_fs1_by, v_fs2_b, v_fs2_by,
v_fs3_b, v_fs3_by, v_fs4_b, v_fs4_by, v_full_b, v_full_by);
DBMS_OUTPUT.PUT_LINE('Unformatted Blocks = '||v_unf_b);
DBMS_OUTPUT.PUT_LINE('FS1 Blocks (0-25%) = '||v_fs1_b);
DBMS_OUTPUT.PUT_LINE('FS2 Blocks (25-50%) = '||v_fs2_b);
DBMS_OUTPUT.PUT_LINE('FS3 Blocks (50-75%) = '||v_fs3_b);
DBMS_OUTPUT.PUT_LINE('FS4 Blocks (75-100%) = '||v_fs4_b);
DBMS_OUTPUT.PUT_LINE('Full Blocks = '||v_full_b);
END;
/
五、操作建議與限制
- ONLINE 運維:
SHRINK SPACE COMPACT 不鎖表,可業務低峰執行;SHRINK SPACE 需短暫排他鎖,安排維護窗口。
- 索引同步:CASCADE 會遞歸 SHRINK 所有依賴索引,耗時較長,建議分步驟。
- 無法 SHRINK 的物件:含 LONG/LOB 欄位的表 (需
ALTER TABLE ... MOVE LOB)、叢集表、啟用 Row Movement 仍報錯的表。
- UNDO/TEMP 表空間:不可 SHRINK,需重建表空間。
- 備份考量:SHRINK 後建議執行 Level 0 備份,因資料塊位置大幅變動。