Oracle 使用者表空間查詢完全指南：核心視圖、空間使用率、權限與自動化腳本

核心資料字典視圖對照表

常用查詢語句庫

1. 查詢當前使用者所有表及其表空間

SELECT table_name, tablespace_name, num_rows, blocks, last_analyzed
FROM user_tables
ORDER BY tablespace_name, table_name;

2. 查詢所有使用者的預設/暫存表空間

SELECT username, default_tablespace, temporary_tablespace, profile, account_status
FROM dba_users
ORDER BY username;

3. 查詢特定使用者的表空間配額

SELECT tablespace_name, 
       MAX_BYTES/1024/1024 AS max_mb,
       CASE WHEN MAX_BYTES = -1 THEN 'UNLIMITED' ELSE TO_CHAR(MAX_BYTES/1024/1024) END AS quota_mb
FROM dba_ts_quotas
WHERE username = 'APP_USER';

4. 表空間使用率詳細報表 (核心管理查詢)

COL tablespace_name FOR A30
COL total_gb FOR 999,999.99
COL used_gb FOR 999,999.99
COL free_gb FOR 999,999.99
COL pct_used FOR 999.99

SELECT 
  df.tablespace_name,
  ROUND(df.total_bytes/1024/1024/1024, 2) AS total_gb,
  ROUND((df.total_bytes - NVL(fs.free_bytes, 0))/1024/1024/1024, 2) AS used_gb,
  ROUND(NVL(fs.free_bytes, 0)/1024/1024/1024, 2) AS free_gb,
  ROUND(100 * (df.total_bytes - NVL(fs.free_bytes, 0)) / df.total_bytes, 2) AS pct_used
FROM (
  SELECT tablespace_name, SUM(bytes) AS total_bytes
  FROM dba_data_files
  GROUP BY tablespace_name
) df
LEFT JOIN (
  SELECT tablespace_name, SUM(bytes) AS free_bytes
  FROM dba_free_space
  GROUP BY tablespace_name
) fs ON df.tablespace_name = fs.tablespace_name
ORDER BY pct_used DESC;

5. 含自動擴展資訊的表空間報表

SELECT 
  df.tablespace_name,
  ROUND(SUM(df.bytes)/1024/1024/1024, 2) AS total_gb,
  ROUND(SUM(CASE WHEN df.autoextensible = 'YES' THEN df.maxbytes ELSE df.bytes END)/1024/1024/1024, 2) AS max_gb,
  ROUND((SUM(df.bytes) - NVL(fs.free_bytes, 0))/1024/1024/1024, 2) AS used_gb,
  ROUND(100 * (SUM(df.bytes) - NVL(fs.free_bytes, 0)) / SUM(df.bytes), 2) AS pct_used,
  CASE WHEN SUM(CASE WHEN df.autoextensible = 'YES' THEN 1 ELSE 0 END) > 0 THEN 'YES' ELSE 'NO' END AS autoextend
FROM dba_data_files df
LEFT JOIN (
  SELECT tablespace_name, SUM(bytes) AS free_bytes
  FROM dba_free_space
  GROUP BY tablespace_name
) fs ON df.tablespace_name = fs.tablespace_name
GROUP BY df.tablespace_name, fs.free_bytes
ORDER BY pct_used DESC;

6. 使用者物件空間佔用排名 (Top 20)

SELECT owner, segment_name, segment_type, tablespace_name,
       ROUND(bytes/1024/1024, 2) AS size_mb
FROM dba_segments
WHERE owner NOT IN ('SYS','SYSTEM','SYSMAN','DBSNMP','OUTLN','CTXSYS','MDSYS','ORDSYS')
ORDER BY bytes DESC
FETCH FIRST 20 ROWS ONLY;  -- 12c+ 語法
-- 11g: WHERE ROWNUM <= 20

7. 特定使用者在各表空間的物件分布

SELECT tablespace_name, segment_type, COUNT(*) AS cnt,
       ROUND(SUM(bytes)/1024/1024, 2) AS total_mb
FROM dba_segments
WHERE owner = 'APP_USER'
GROUP BY tablespace_name, segment_type
ORDER BY tablespace_name, total_mb DESC;

8. 當前連線 Session 與其使用表空間

SELECT s.sid, s.serial#, s.username, s.program, s.module,
       s.sql_id, t.tablespace_name
FROM v$session s
LEFT JOIN v$tempseg_usage t ON s.saddr = t.session_addr
WHERE s.username IS NOT NULL
ORDER BY s.username;

9. 暫存表空間使用情況

SELECT tablespace_name,
       ROUND(SUM(bytes_used)/1024/1024, 2) AS used_mb,
       ROUND(SUM(bytes_free)/1024/1024, 2) AS free_mb
FROM v$temp_space_header
GROUP BY tablespace_name;

自動化監控腳本

表空間使用率告警 (超過 85% 發送郵件)

-- check_tablespace_alert.sql
SET SERVEROUTPUT ON
DECLARE
  v_threshold NUMBER := 85;
  v_mail_body CLOB := 'Tablespace Alert:\n\n';
  v_alert_count NUMBER := 0;
BEGIN
  FOR t IN (
    SELECT df.tablespace_name,
           ROUND(100 * (df.total_bytes - NVL(fs.free_bytes, 0)) / df.total_bytes, 2) AS pct_used
    FROM (
      SELECT tablespace_name, SUM(bytes) AS total_bytes
      FROM dba_data_files GROUP BY tablespace_name
    ) df
    LEFT JOIN (
      SELECT tablespace_name, SUM(bytes) AS free_bytes
      FROM dba_free_space GROUP BY tablespace_name
    ) fs ON df.tablespace_name = fs.tablespace_name
    WHERE ROUND(100 * (df.total_bytes - NVL(fs.free_bytes, 0)) / df.total_bytes, 2) >= v_threshold
  ) LOOP
    v_mail_body := v_mail_body || t.tablespace_name || ': ' || t.pct_used || '% used\n';
    v_alert_count := v_alert_count + 1;
  END LOOP;
  
  IF v_alert_count > 0 THEN
    -- 實務中呼叫 UTL_MAIL 或外部腳本發送
    DBMS_OUTPUT.PUT_LINE('ALERT: ' || v_alert_count || ' tablespaces over threshold');
    DBMS_OUTPUT.PUT_LINE(v_mail_body);
    -- UTL_MAIL.SEND(sender => 'dba@corp.com', recipients => 'dba-team@corp.com', 
    --               subject => 'Tablespace Alert', message => v_mail_body);
  END IF;
END;
/

使用者配額不足預警

SELECT u.username, q.tablespace_name,
       ROUND(q.bytes/1024/1024, 2) AS used_mb,
       ROUND(q.max_bytes/1024/1024, 2) AS quota_mb,
       ROUND(100 * q.bytes / NULLIF(q.max_bytes, 0), 2) AS pct_used
FROM dba_ts_quotas q
JOIN dba_users u ON q.username = u.username
WHERE q.max_bytes > 0  -- 排除 UNLIMITED
AND q.bytes / q.max_bytes > 0.9  -- 90% 閾值
AND u.account_status = 'OPEN'
ORDER BY pct_used DESC;

權限需求說明

常見誤區

1. 誤區：USER_TABLES 顯示所有表空間。
   事實：僅顯示當前使用者擁有的表。
2. 誤區：DBA_FREE_SPACE 反映即時可用空間。
   事實：未扣除 Undo、暫存段、尚未提交的交易空間。
3. 誤區：配額 (QUOTA) 限制總表空間使用。
   事實：配額限制「該使用者在該表空間」的用量，不影響其他使用者。
4. 誤區：BYTES 欄位單位是 MB。
   事實：單位是 Bytes，需除以 1024*1024 換算 MB。

Enterprise Manager / SQL Developer 視覺化替代方案

• EM Express: https://host:5500/em → 儲存空間 → 表空間
• SQL Developer: DBA 面板 → 儲存空間 → 表空間使用率圖表
• OEM Cloud Control: 目標 → 資料庫 → 儲存空間

最佳實務建議

• 建立標準化監控 View 供報表工具 (Grafana/PowerBI) 串接
• 表空間使用率分級：<70% 正常、70-85% 關注、>85% 告警、>95% 緊急擴容
• 配合 AUTOEXTEND 設定 MAXSIZE 防止無限增長佔滿磁碟
• 定期執行 ALTER TABLESPACE ... COALESCE 整理碎片 (字典管理) 或確認 LMT 自動管理
• 大表分區策略：不同分區置於不同表空間/磁碟，平衡 I/O