Oracle表空間監控兩大SQL方案與擴容操作實戰指南

為何要監控表空間？

表空間剩餘比例低於10%即屬預警區，若耗盡將導致ORA-01653/01654錯誤，業務寫入中斷。建議納入監控系統（Zabbix/Prometheus/EM）每5分鐘採集一次。

方案一：DBA_DATA_FILES + DBA_FREE_SPACE 聯合查詢（最常用）

SELECT dbf.tablespace_name,
       ROUND(dbf.totalspace, 2)            "總量(MB)",
       dbf.totalblocks                       "總塊數",
       ROUND(dfs.freespace, 2)               "剩餘(MB)",
       dfs.freeblocks                        "剩餘塊數",
       ROUND((dfs.freespace / dbf.totalspace) * 100, 2) "空閒比例(%)"
FROM   (SELECT tablespace_name,
               SUM(bytes)/1024/1024 totalspace,
               SUM(blocks) totalblocks
        FROM   dba_data_files
        GROUP BY tablespace_name) dbf,
       (SELECT tablespace_name,
               SUM(bytes)/1024/1024 freespace,
               SUM(blocks) freeblocks
        FROM   dba_free_space
        GROUP BY tablespace_name) dfs
WHERE  TRIM(dbf.tablespace_name) = TRIM(dfs.tablespace_name)
ORDER BY "空閒比例(%)" ASC;

優點：邏輯清晰、相容9i+、可直接納入報表。

方案二：V$TABLESPACE + V$DATAFILE 動態視圖查詢（含暫存表空間）

SELECT t.name "Tablespace Name",
       ROUND(f.free_space, 2) "Free(MB)",
       ROUND(t.total_space - f.free_space, 2) "Used(MB)",
       ROUND(t.total_space, 2) "Total(MB)",
       ROUND((f.free_space / t.total_space) * 100, 2) "Free(%)"
FROM   (SELECT tablespace_name, SUM(bytes)/1024/1024 free_space
        FROM   sys.dba_free_space
        GROUP BY tablespace_name) f,
       (SELECT b.name, SUM(bytes)/1024/1024 total_space
        FROM   sys.v_$datafile a, sys.v_$tablespace b
        WHERE  a.ts# = b.ts#
        GROUP BY b.name) t
WHERE  f.tablespace_name = t.name
UNION ALL
-- Temporary tablespace
SELECT tablespace_name,
       ROUND(SUM(bytes_free)/1024/1024, 2),
       ROUND(SUM(bytes_used)/1024/1024, 2),
       ROUND(SUM(bytes_free+bytes_used)/1024/1024, 2),
       ROUND(SUM(bytes_free)/SUM(bytes_free+bytes_used)*100, 2)
FROM   v$temp_space_header
GROUP BY tablespace_name
ORDER BY "Free(%)" ASC;

優點：涵蓋暫存表空間、即時反映實際使用量（含未提交交易）。

表空間不足三種擴容手段

1. 查詢資料檔路徑

SELECT file_name, tablespace_name, bytes/1024/1024 "Size(MB)"
FROM   dba_data_files
WHERE  tablespace_name = 'ARD';

2. 調整既有資料檔大小（需檔案系統有剩餘空間）

ALTER DATABASE DATAFILE '/u01/oradata/ARD01.dbf' RESIZE 4096M;

3. 新增資料檔（推薦，可分散I/O）

ALTER TABLESPACE ARD
ADD DATAFILE '/u02/oradata/ARD02.dbf' SIZE 2048M AUTOEXTEND ON NEXT 100M MAXSIZE 8192M;

最佳實踐清單

• 資料檔單檔不超過 2GB（32位）或 32GB（64位），便於備份/還原/傳輸。
• 啟用AUTOEXTEND ON MAXSIZE ...防止突發寫入爆滿。
• 關鍵表空間（SYSTEM、SYSAUX、UNDO、TEMP）設定閾值告警 15%。
• 定期執行ALTER TABLESPACE ... COALESCE整理碎片（Locally Managed Tablespace通常不需）。