Oracle 表空間管理全攻略：查詢、建立、擴展、監控與遷移

表空間是 Oracle 邏輯儲存單元，掌握其全生命週期管理是 DBA 核心技能。本文整理查詢、建立、擴展、狀態控制、刪除、遷移與監控腳本。

一、查詢監控類

1. 表空間使用率 (最常用)

SELECT 
  d.tablespace_name,
  ROUND(d.total_mb, 2)       AS 總大小_MB,
  ROUND(d.total_mb - f.free_mb, 2) AS 已用_MB,
  ROUND(f.free_mb, 2)        AS 可用_MB,
  ROUND((d.total_mb - f.free_mb) / d.total_mb * 100, 2) || '%' AS 使用率
FROM (
  SELECT tablespace_name, SUM(bytes)/1024/1024 AS total_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 使用率 DESC;

2. 檔案級明細

SELECT file_id, tablespace_name, file_name,
       ROUND(bytes/1024/1024, 2) AS size_mb,
       AUTOEXTENSIBLE, MAXBYTES/1024/1024 AS max_mb
FROM dba_data_files ORDER BY tablespace_name, file_id;

二、建立類

三、狀態控制

ALTER TABLESPACE app_data OFFLINE [NORMAL | TEMPORARY | IMMEDIATE | FOR RECOVER];
ALTER TABLESPACE app_data ONLINE;
ALTER TABLESPACE app_data READ ONLY;
ALTER TABLESPACE app_data READ WRITE;

四、擴展與遷移

1. 新增資料檔：ALTER TABLESPACE app_data ADD DATAFILE '/u01/app_data02.dbf' SIZE 500M;
2. 手動調整大小：ALTER DATABASE DATAFILE '/u01/app_data01.dbf' RESIZE 2G;
3. 啟用自動擴展：ALTER DATABASE DATAFILE '/u01/app_data01.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE 5G;
4. 遷移資料檔 (Open 狀態)：
   ALTER TABLESPACE app_data OFFLINE;
   OS 層 mv 檔案
   ALTER TABLESPACE app_data RENAME DATAFILE 'old' TO 'new';
ALTER TABLESPACE app_data ONLINE;

五、刪除

DROP TABLESPACE app_data INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

六、自動化監控腳本 (建議納入排程)

-- 使用率 > 85% 發送告警
SELECT tablespace_name, 
       ROUND(used_pct, 2) AS pct_used
FROM (
  SELECT d.tablespace_name,
         (d.total_mb - f.free_mb) / d.total_mb * 100 AS used_pct
  FROM ... -- 同上查詢
) WHERE used_pct > 85;

七、常用視圖速查

• DBA_TABLESPACES、DBA_DATA_FILES、DBA_FREE_SPACE、DBA_TEMP_FILES、V$TABLESPACE、V$DATAFILE

建立「每日使用率報表 + 85% 告警 + 季度容量規劃」三位一體機制，表空間永不爆滿。