Oracle 表空間監控核心查詢：自動擴展狀態、大小、剩餘空間、預設表空間四大金鑰指標

表空間空間管理的核心在於「知現狀、控增長、防寫滿」。本文提供四條生產級監控查詢，涵蓋自動擴展狀態、配置大小、剩餘空間、用戶預設表空間，適合納入每日巡檢腳本與監控儀表板。

一、檢查自動擴展狀態

SELECT file_name, tablespace_name, autoextensible
FROM dba_data_files
ORDER BY tablespace_name, file_id;

• AUTOEXTENSIBLE = YES：已啟用自動擴展
• 建議搭配 MAXSIZE 防止無限增長佔滿磁碟

二、查看表空間配置總大小 (MB)

SELECT tablespace_name, SUM(bytes)/1024/1024 AS size_mb
FROM dba_data_files
GROUP BY tablespace_name
ORDER BY size_mb DESC;

三、查看表空間剩餘空間 (MB)

SELECT tablespace_name, 
       COUNT(*) AS extent_count,
       ROUND(SUM(bytes)/1024/1024, 2) AS free_mb,
       SUM(blocks) AS total_blocks
FROM dba_free_space
GROUP BY tablespace_name
ORDER BY free_mb ASC;  -- 剩餘最少者優先顯示

關鍵指標：FREE_MB 低於閾值 (如 10% 或 5GB) 觸發告警。

四、查看當前用戶預設表空間

SELECT username, default_tablespace
FROM user_users;  -- 僅當前用戶
-- 或全庫用戶
SELECT username, default_tablespace FROM dba_users;

確保應用用戶預設表空間指向業務表空間，避免誤建物件至 SYSTEM/SYSAUX。

五、進階：表空間使用率儀表板查詢

SELECT 
  d.tablespace_name,
  ROUND(d.total_mb, 2) AS total_mb,
  ROUND(f.free_mb, 2) AS free_mb,
  ROUND(d.total_mb - f.free_mb, 2) AS used_mb,
  ROUND((d.total_mb - f.free_mb) / d.total_mb * 100, 2) AS pct_used,
  d.autoextensible,
  d.max_mb
FROM (
  SELECT tablespace_name, 
         SUM(bytes)/1024/1024 AS total_mb,
         MAX(autoextensible) AS autoextensible,
         SUM(CASE WHEN autoextensible='YES' THEN maxbytes ELSE bytes END)/1024/1024 AS max_mb
  FROM dba_data_files
  GROUP BY tablespace_name
) d
JOIN (
  SELECT tablespace_name, SUM(bytes)/1024/1024 AS free_mb
  FROM dba_free_space
  GROUP BY tablespace_name
) f ON d.tablespace_name = f.tablespace_name
ORDER BY pct_used DESC;

六、告警閾值建議

將上述查詢封裝為 Shell/Python/PL/SQL 巡檢腳本，配合 Enterprise Manager / Prometheus / Grafana 實現可視化告警，將表空間管理從被動救火轉為主動管控。