Oracle表空間使用率監控查詢與碎片整理指南

表空間使用率標準查詢

SELECT df.tablespace_name "表空間",
       (df.totalspace - NVL(fs.freespace, 0)) "已使用(MB)",
       NVL(fs.freespace, 0) "剩餘(MB)",
       df.totalspace "總計(MB)",
       ROUND(100 * (1 - NVL(fs.freespace, 0) / df.totalspace), 2) "使用率(%)"
FROM (
    SELECT tablespace_name, ROUND(SUM(bytes)/1048576) totalspace
    FROM dba_data_files GROUP BY tablespace_name
) df,
(
    SELECT tablespace_name, ROUND(SUM(bytes)/1048576) freespace
    FROM dba_free_space GROUP BY tablespace_name
) fs
WHERE df.tablespace_name = fs.tablespace_name(+)
ORDER BY 5 DESC;

關鍵點：外連接(+)確保無空閒空間表空間也顯示，單位轉換為MB(1048576=1024²)。

表空間碎片指數(FSFI)檢測

SELECT tablespace_name,
       SQRT(MAX(blocks)/SUM(blocks)) * (100/SQRT(SQRT(COUNT(blocks)))) FSFI
FROM dba_free_space
GROUP BY tablespace_name
ORDER BY FSFI DESC;

FSFI解讀

• FSFI > 25：嚴重碎片，需整理
• 10 < FSFI ≤ 25：中度碎片，關注趨勢
• FSFI ≤ 10：健康狀態

原理：綜合考慮自由區塊最大值、總和、數量三個維度。

碎片整理三大方案

方案一：自動合並 (SMON)

ALTER TABLESPACE ts_name DEFAULT STORAGE (PCTINCREASE 1);

設置PCTINCREASE > 0啟用SMON後台進程定期合並相鄰自由區塊。PCTINCREASE=1為推薦值。

方案二：手動合並

ALTER TABLESPACE ts_name COALESCE;

立即合並該表空間所有相鄰自由區塊，不移動數據，在線操作，鎖定時間極短。

方案三：數據搬移重組 (終極方案)

-- 1. 創建新表空間
CREATE TABLESPACE ts_new DATAFILE '...' SIZE 10G AUTOEXTEND ON;

-- 2. 移動表/索引 (在線可用DBMS_REDEFINITION)
ALTER TABLE big_table MOVE TABLESPACE ts_new;
ALTER INDEX big_idx REBUILD TABLESPACE ts_new;

-- 3. 刪除舊表空間 (確認無對象後)
DROP TABLESPACE ts_old INCLUDING CONTENTS AND DATAFILES;

監控自動化建議

• 設置閾值告警：使用率>85%發送郵件/短信
• 每日任務收集使用率趨勢，預測擴容時間點
• 結合DBA_SEGMENTS定位大對象、高增長對象
• 大文件表空間(Bigfile)簡化管理，但單文件上限受OS限制

常見問題排查