SQL 查詢 Oracle 使用者預設表空間使用狀況：一條語句掌握空間健康度

需求背景

DBA 需快速掌握每位使用者預設表空間的實際使用率，以便規劃擴容或清理。本文提供單一查詢整合使用者預設表空間與表空間空間統計。

核心查詢語句

SELECT 
    ut.username,
    ut.default_tablespace,
    tsu.表空間名,
    tsu.字節數 AS 總大小_M,
    tsu.已使用 AS 已用_M,
    tsu.剩余空間 AS 剩餘_M,
    tsu.占用百分比 AS 使用率_百分比
FROM 
    (SELECT username, default_tablespace FROM dba_users) ut
JOIN 
    (SELECT 
         b.tablespace_name AS 表空間名,
         ROUND(b.bytes/1024/1024, 2) || 'M' AS 字節數,
         ROUND((b.bytes - SUM(NVL(a.bytes,0)))/1024/1024, 2) || 'M' AS 已使用,
         ROUND(SUM(NVL(a.bytes,0))/1024/1024, 2) || 'M' AS 剩余空間,
         ROUND(100 - SUM(NVL(a.bytes,0))/b.bytes*100, 2) AS 占用百分比
     FROM dba_free_space a, dba_data_files b
     WHERE a.file_id = b.file_id
     GROUP BY b.tablespace_name, b.file_id, b.bytes
    ) tsu
ON ut.default_tablespace = tsu.表空間名
ORDER BY ut.username;

語句拆解與關鍵點

1. 子查詢 ut：從 DBA_USERS 取得使用者與其預設表空間。
2. 子查詢 tsu：聯合 DBA_FREE_SPACE（剩餘區塊）與 DBA_DATA_FILES（資料檔大小），按表空間彙總計算總量、已用、剩餘、使用率。
3. JOIN 條件：ut.default_tablespace = tsu.表空間名，精準對應使用者預設表空間。
4. 單位換算：Bytes → MB（除以 1024*1024），並四捨五入保留兩位小數，附加 'M' 單位標識。
5. 空值處理：NVL(a.bytes,0) 防止全新表空間無剩餘記錄導致 SUM 為 NULL。

進階擴充建議

• 加入臨時表空間：同理 JOIN DBA_TEMP_FILES 與 V$TEMP_SPACE_HEADER 監控臨時表空間。
• 設定閾值告警：外層包裝 WHERE 占用百分比 > 85 直接輸出需關注對象。
• 歷史趨勢：定期將結果寫入監控表，繪製空間成長曲線。
• 多租戶 (CDB/PDB)：12c 以上環境請改查 CDB_USERS、CDB_FREE_SPACE、CDB_DATA_FILES。

輸出範例

總結

此查詢整合使用者屬性與表空間物理統計，單一語句即可產出「使用者-預設表空間-使用率」三維視圖，適合納入日常巡檢腳本或監控儀表板，助力主動容量管理。