前言：表空間是資料庫儲存的邏輯容器
掌握表空間的建立、調整、刪除操作，是 DBA 日常維運的核心技能。本文提供生產級標準範本，涵蓋永久表空間、臨時表空間、Undo 表空間、Bigfile 表空間、自動擴展策略、刪除級聯處理，並針對常見錯誤（ORA-01543、ORA-03297、ORA-02260）給出預防與解法。
一、永久表空間建立標準範本
Smallfile (傳統) 表空間
CREATE TABLESPACE app_data
DATAFILE '/u01/oradata/ORCL/app_data01.dbf' SIZE 1G
AUTOEXTEND ON NEXT 512M MAXSIZE 32G
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO
DEFAULT COMPRESS FOR OLTP
ONLINE;
Bigfile 表空間（超大資料倉儲、單檔案管理）
CREATE BIGFILE TABLESPACE dw_data
DATAFILE '/u02/oradata/ORCL/dw_data01.dbf' SIZE 100G
AUTOEXTEND ON NEXT 10G MAXSIZE 10T
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO
DEFAULT COMPRESS FOR QUERY HIGH
ONLINE;
關鍵子句解析
| 子句 | 推薦值 | 理由 |
|---|
| EXTENT MANAGEMENT | LOCAL AUTOALLOCATE | 位圖管理、系統自動決定 Extent 大小，消除手動調優 |
| SEGMENT SPACE MANAGEMENT | AUTO (ASSM) | 位圖管理自由空間，消除 freelist 競爭，高併發插入效能最佳 |
| DEFAULT COMPRESS | FOR OLTP / FOR QUERY HIGH | Advanced Compression 選項啟用時，自動壓縮新資料，節省 50-80% 空間 |
| AUTOEXTEND | ON NEXT 512M MAXSIZE 32G | 避免滿表空間報錯；MAXSIZE 防止失控佔滿磁碟 |
二、臨時表空間建立
CREATE TEMPORARY TABLESPACE app_temp
TEMPFILE '/u01/oradata/ORCL/app_temp01.dbf' SIZE 2G
AUTOEXTEND ON NEXT 512M MAXSIZE 16G
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
TEMPFILE：不記錄 Redo，不納入備份，啟動時自動重建（內容丟失不影響恢復）。UNIFORM SIZE 1M：統一 Extent 大小，適合大量排序/雜湊操作，減少碎片。- 建議：每個業務 Schema 群組配置專屬臨時表空間，避免單一 TEMP 成為熱點。
三、Undo 表空間建立
CREATE UNDO TABLESPACE undotbs2
DATAFILE '/u01/oradata/ORCL/undotbs02.dbf' SIZE 4G
AUTOEXTEND ON NEXT 1G MAXSIZE 32G
RETENTION GUARANTEE; -- 保證長查詢/閃回不被覆寫
- 資料庫同一時刻僅能有一個 Undo 表空間生效（
ALTER SYSTEM SET UNDO_TABLESPACE=undotbs2 切換）。
RETENTION GUARANTEE 確保 UNDO_RETENTION 參數生效，防止長查詢 ORA-01555。
四、表空間擴容與資料檔管理
手動擴容
ALTER DATABASE DATAFILE '/u01/oradata/ORCL/app_data01.dbf' RESIZE 2G;
新增資料檔
ALTER TABLESPACE app_data
ADD DATAFILE '/u02/oradata/ORCL/app_data02.dbf' SIZE 1G
AUTOEXTEND ON NEXT 512M MAXSIZE 32G;
啟用/關閉自動擴展
ALTER DATABASE DATAFILE '/u01/oradata/ORCL/app_data01.dbf' AUTOEXTEND ON NEXT 512M MAXSIZE 32G;
ALTER DATABASE DATAFILE '/u01/oradata/ORCL/app_data01.dbf' AUTOEXTEND OFF;
五、表空間離線/線上/唯讀
ALTER TABLESPACE app_data OFFLINE NORMAL; -- 或 IMMEDIATE/TEMPORARY
ALTER TABLESPACE app_data ONLINE;
ALTER TABLESPACE app_data READ ONLY;
ALTER TABLESPACE app_data READ WRITE;
OFFLINE NORMAL 需檢查點完整，適合計劃性維護。READ ONLY 適用於歷史歸檔表空間，備份後可設為唯讀節省恢復時間。
六、刪除表空間：級聯處理避坑指南
-- 完整刪除：含內容、資料檔、外鍵參照
DROP TABLESPACE app_data
INCLUDING CONTENTS AND DATAFILES
CASCADE CONSTRAINTS;
三大子句必要性
| 子句 | 缺少時後果 | ORA 錯誤 |
|---|
| INCLUDING CONTENTS | 表空間非空無法刪除 | ORA-01543: tablespace not empty |
| AND DATAFILES | OS 層資料檔殘留，佔用磁碟 | 無錯誤，但需手動 rm |
| CASCADE CONSTRAINTS | 其他表空間表有 FK 指向此表空間表，刪除失敗 | ORA-02260: referential integrity constraint violated |
刪除前檢查清單
-- 1. 確認無其他用戶使用此表空間
SELECT owner, segment_name, segment_type
FROM dba_segments WHERE tablespace_name = 'APP_DATA';
-- 2. 確認無跨表空間外鍵
SELECT a.table_name, a.constraint_name, c_pk.table_name ref_table,
c_pk.owner ref_owner, c_pk.tablespace_name ref_ts
FROM dba_constraints a
JOIN dba_constraints c_pk ON a.r_constraint_name = c_pk.constraint_name
WHERE a.constraint_type = 'R'
AND a.tablespace_name = 'APP_DATA'
AND c_pk.tablespace_name != 'APP_DATA';
七、用戶與表空間綁定
CREATE USER app_user IDENTIFIED BY xxx
DEFAULT TABLESPACE app_data
TEMPORARY TABLESPACE app_temp
QUOTA UNLIMITED ON app_data;
-- 或指定配額
ALTER USER app_user QUOTA 10G ON app_data;
八、常見錯誤速查表
| 錯誤 | 原因 | 解法 |
|---|
| ORA-01652: unable to extend temp segment | 臨時表空間不足 | 擴充 TEMP、優化 SQL 排序/雜湊、增加 PGA |
| ORA-01536: space quota exceeded | 用戶配額用盡 | ALTER USER ... QUOTA UNLIMITED ON ts |
| ORA-03297: file contains used data beyond requested RESIZE value | 縮減資料檔時檔案尾端有資料 | 先移動段（SHRINK/ALTER TABLE MOVE）再縮減 |
| ORA-01543: tablespace not empty | 刪除非空表空間未加 INCLUDING CONTENTS | 加上子句或先清空段 |
結語
表空間管理的核心在於預留成長空間（AUTOEXTEND+MAXSIZE）、採用現代管理模式（LMT+ASSM）、刪除時完整級聯（INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS）。將上述範本納入團隊 DBA 操作手冊，可避免 95% 的儲存相關故障。
