Oracle表空間與數據文件全維度查詢指南：從EM到SQL的DBA必備工具箱

核心概念：邏輯與物理的映射關係

• 表空間： 邏輯存儲單位，由1個或多個數據文件組成；
• 數據文件： 物理存儲單位，屬於且僅屬於1個表空間；
• 段/區/塊： 表空間→段→區→塊，層層遞進的空間分配層級。

方法一：Enterprise Manager Console（圖形化，適合巡檢）

1. 啟動：开始 → 程序 → Oracle-OraHome92 → Enterprise Manager Console；
2. 登錄：獨立模式 → SYS/SYSDBA → 目標實例；
3. 導航：存儲 → 表空間，即時查看：名稱、類型、區管理、大小(MB)、已用、利用率；
4. 鑽取：雙擊表空間查看數據文件分佈、自動擴展屬性、段分佈熱力圖。

方法二：SQL*Plus標準查詢腳本庫（自動化、腳本化首選）

1. 表空間整體視圖（含利用率、自動擴展、塊大小）

-- ts_overview.sql
SET LINES 200 PAGES 1000
COL tablespace_name FOR A20
COL extent_management FOR A15
COL allocation_type FOR A12
COL segment_space FOR A12

SELECT 
  t.tablespace_name,
  t.extent_management,
  t.allocation_type,
  t.segment_space_management,
  ROUND(t.block_size/1024,0) AS BLK_KB,
  ROUND(SUM(d.bytes)/1024/1024,2) AS TOTAL_MB,
  ROUND(SUM(CASE WHEN d.autoextensible='YES' THEN d.maxbytes ELSE d.bytes END)/1024/1024,2) AS MAX_MB,
  ROUND(SUM(NVL(u.bytes,0))/1024/1024,2) AS USED_MB,
  ROUND(100*SUM(NVL(u.bytes,0))/SUM(CASE WHEN d.autoextensible='YES' THEN d.maxbytes ELSE d.bytes END),1) AS PCT_USED
FROM dba_tablespaces t
JOIN dba_data_files d ON t.tablespace_name = d.tablespace_name
LEFT JOIN (
  SELECT tablespace_name, SUM(bytes) bytes
  FROM dba_segments
  GROUP BY tablespace_name
) u ON t.tablespace_name = u.tablespace_name
GROUP BY t.tablespace_name, t.extent_management, t.allocation_type, t.segment_space_management, t.block_size
ORDER BY PCT_USED DESC;

2. 數據文件詳細屬性（路徑、大小、自動擴展、增量）

-- df_detail.sql
COL file_name FOR A60
COL tablespace_name FOR A20
SELECT 
  file_id, tablespace_name, file_name,
  ROUND(bytes/1024/1024,2) AS CUR_MB,
  ROUND(maxbytes/1024/1024,2) AS MAX_MB,
  autoextensible,
  ROUND(increment_by*8192/1024/1024,2) AS INC_MB,
  status, online_status
FROM dba_data_files
ORDER BY tablespace_name, file_id;

3. 臨時表空間專用查詢（使用v$temp_space_header）

-- temp_ts.sql
SELECT 
  t.tablespace_name,
  ROUND(SUM(h.bytes_used)/1024/1024,2) AS USED_MB,
  ROUND(SUM(h.bytes_free)/1024/1024,2) AS FREE_MB,
  ROUND(100*SUM(h.bytes_used)/SUM(h.bytes_used+h.bytes_free),1) AS PCT_USED
FROM dba_tablespaces t
JOIN v$temp_space_header h ON t.tablespace_name = h.tablespace_name
WHERE t.contents = 'TEMPORARY'
GROUP BY t.tablespace_name;

4. 表空間碎片化分析（空閒區分佈）

-- ts_fragmentation.sql
SELECT 
  tablespace_name,
  COUNT(*) AS FREE_EXTENTS,
  ROUND(MIN(bytes)/1024/1024,2) AS MIN_EXT_MB,
  ROUND(MAX(bytes)/1024/1024,2) AS MAX_EXT_MB,
  ROUND(AVG(bytes)/1024/1024,2) AS AVG_EXT_MB,
  ROUND(SUM(bytes)/1024/1024,2) AS TOTAL_FREE_MB
FROM dba_free_space
GROUP BY tablespace_name
HAVING MAX(bytes) < 100*1024*1024  -- 最大空閒區<100MB，潛在碎片風險
ORDER BY MAX_EXT_MB;

方法三：自動化巡檢腳本（Shell + SQL，生產級部署）

#!/bin/bash
# check_tablespace.sh - 表空間利用率告警腳本
THRESHOLD=85
ALERT_FILE="/tmp/ts_alert_$(date +%Y%m%d).log"

sqlplus -s / as sysdba < $ALERT_FILE
SET HEAD OFF FEED OFF PAGES 0
SELECT 'ALERT: '||tablespace_name||' at '||ROUND(pct_used,1)||'%'
FROM (
  SELECT t.tablespace_name,
         100*SUM(NVL(u.bytes,0))/SUM(CASE WHEN d.autoextensible='YES' THEN d.maxbytes ELSE d.bytes END) AS pct_used
  FROM dba_tablespaces t
  JOIN dba_data_files d ON t.tablespace_name = d.tablespace_name
  LEFT JOIN (SELECT tablespace_name, SUM(bytes) bytes FROM dba_segments GROUP BY tablespace_name) u
    ON t.tablespace_name = u.tablespace_name
  GROUP BY t.tablespace_name
)
WHERE pct_used > $THRESHOLD;
EOF

if [ -s "$ALERT_FILE" ]; then
  mail -s "【告警】表空間利用率超過${THRESHOLD}%" dba_team@company.com < $ALERT_FILE
fi

進階管理操作速查

結語

掌握EM可視化巡檢 + SQL腳本庫精準分析 + Shell自動化告警三位一體的表空間管理體系，是DBA從「被動滅火」轉向「主動治理」的關鍵。建議將上述腳本納入每日巡檢作業庫，並建立表空間容量規劃模型（基於歷史增長趨勢預測30/60/90天需求），實現存儲資源的精準投遞。