Oracle表空間與資料檔案查詢實戰：SQL腳本、EM工具、臨時表空間完全指南

一、表空間查詢：命令列SQL腳本 (核心技能)

無圖形介面環境下的標準作法，查詢三大字典視圖：

• DBA_FREE_SPACE：各表空間剩餘空間
• DBA_DATA_FILES：資料檔案大小
• DBA_TABLESPACES：表空間屬性 (類型、區管理)

標準查詢腳本 (含利用率%)

SELECT a.tablespace_name "表空間名稱",
       c.contents "類型",
       c.extent_management "區管理",
       ROUND(b.total_bytes/1024/1024,2) "總大小(MB)",
       ROUND((b.total_bytes - a.free_bytes)/1024/1024,2) "已用(MB)",
       ROUND((b.total_bytes - a.free_bytes)/b.total_bytes*100,2) "利用率(%)"
FROM (
  SELECT tablespace_name, SUM(NVL(bytes,0)) free_bytes
  FROM dba_free_space GROUP BY tablespace_name
) a,
(
  SELECT tablespace_name, SUM(bytes) total_bytes
  FROM dba_data_files GROUP BY tablespace_name
) b,
(
  SELECT tablespace_name, contents, extent_management
  FROM dba_tablespaces
) c
WHERE a.tablespace_name = b.tablespace_name
  AND b.tablespace_name = c.tablespace_name
ORDER BY "利用率(%)" DESC;

二、資料檔案查詢：逐檔案利用率

SELECT b.file_name "物理檔案",
       b.tablespace_name "表空間",
       ROUND(b.bytes/1024/1024,2) "大小(MB)",
       ROUND((b.bytes - SUM(NVL(a.bytes,0)))/1024/1024,2) "已用(MB)",
       ROUND((b.bytes - SUM(NVL(a.bytes,0)))/b.bytes*100,2) "利用率(%)"
FROM dba_free_space a, dba_data_files b
WHERE a.file_id(+) = b.file_id
GROUP BY b.tablespace_name, b.file_name, b.bytes
ORDER BY b.tablespace_name, "利用率(%)" DESC;

關鍵：a.file_id(+) = b.file_id 外連接，確保空表空間檔案也顯示。

三、Enterprise Manager Console 圖形介面 (有GUI時)

1. 啟動：開始 → 程式 → Oracle → Enterprise Manager Console
2. 登入：選「獨立啟動」 → SYS / 密碼 → 連接身分選 SYSDBA
3. 路徑：儲存 → 表空間 / 儲存 → 資料檔案
4. 顯示：名稱、類型、區管理、大小、已用、利用率%

四、臨時表空間專屬查詢

1. 傳統永久表空間類型的臨時表空間 (9i前)

SELECT a.tablespace_name "表空間", b.bytes/1024/1024 "大小(MB)", b.file_name "檔案"
FROM dba_tablespaces a, dba_data_files b
WHERE a.tablespace_name = b.tablespace_name
  AND a.contents = 'TEMPORARY';

2. 真正的臨時表空間 (9i+ Temporary Tablespace)

使用臨時檔案，資訊在 V$TEMPFILE (非 DBA_DATA_FILES)：

SELECT file#, status, name FROM v$tempfile;

3. 臨時段使用監控 (排序/雜湊/併發)

SELECT tablespace_name, current_users, used_extents, max_used_blocks, max_sort_blocks
FROM v$sort_segment;

五、DBA日常巡檢建議

• 將上述腳本存為 check_tablespace.sql、check_datafiles.sql、check_temp.sql
• 排程 (cron/DBMS_SCHEDULER) 每日/每小時執行，利用率>85%發送告警
• 利用率>90%立即處理：
  1. 清理歷史資料/分區
  2. ALTER TABLESPACE ... ADD DATAFILE ... 擴充
  3. ALTER DATABASE DATAFILE ... RESIZE ... 擴大現有檔案
  4. 啟用AUTOEXTEND ON MAXSIZE UNLIMITED
• 定期檢查 V$TABLESPACE 狀態 (ONLINE/OFFLINE/READ ONLY)

六、快速判斷表空間類型對照

核心原則：熟練命令列腳本是DBA生存技能，GUI只是輔助；建立標準巡檢腳本庫，自動化監控，事半功倍。