Oracle 11g 表空間管理完整指南：核心概念、建立與刪除實戰

表空間是 Oracle 資料庫磁碟管理的最高邏輯層。Oracle 11g 預設必須建立四大核心表空間：SYSTEM、SYSAUX、TEMP、UNDOTBS1。本文系統整理表空間架構、查詢語法、建立/刪除指令與實戰案例，助您掌握存儲管理核心技能。

一、核心表空間功能對照

二、邏輯儲存層級關係

表空間 → 區段 → 區塊 → 資料區塊。一個區段只能駐留在單一表空間；一個區塊只能屬於單一資料檔案。查詢表空間與資料檔案對應關係：

COL TABLESPACE_NAME FOR A10
COL FILE_NAME FOR A50
COL BYTES FOR 999,999,999
SELECT TABLESPACE_NAME, FILE_NAME, BYTES
FROM DBA_DATA_FILES
ORDER BY TABLESPACE_NAME;

三、查詢表空間內物件分布

1. USERS 表空間物件

COL OWNER FOR A10
COL SEGMENT_NAME FOR A30
COL SEGMENT_TYPE FOR A20
SELECT SEGMENT_TYPE, SEGMENT_NAME, OWNER
FROM DBA_SEGMENTS
WHERE TABLESPACE_NAME = 'USERS';

2. SYSAUX 表空間使用者物件統計

SELECT OWNER AS "使用者", COUNT(SEGMENT_NAME) AS "物件數量"
FROM DBA_SEGMENTS
WHERE TABLESPACE_NAME = 'SYSAUX'
GROUP BY OWNER;

四、建立表空間完整語法

CREATE [SMALLFILE | BIGFILE] TABLESPACE tablespace_name
DATAFILE '/path/filename' SIZE n[K|M] REUSE
[,'/path/filename' SIZE n[K|M] REUSE] ...
[AUTOEXTEND [ON|OFF] NEXT n[K|M]]
[MAXSIZE [UNLIMITED | n[K|M]]]
[MINIMUM EXTENT n[K|M]]
[DEFAULT STORAGE storage_clause]
[ONLINE | OFFLINE]
[LOGGING | NOLOGGING]
[PERMANENT | TEMPORARY]
[EXTENT MANAGEMENT DICTIONARY | LOCAL [AUTOALLOCATE | UNIFORM SIZE n[K|M]]];

關鍵參數說明

• SMALLFILE | BIGFILE：小檔案表空間（多檔案）vs 大檔案表空間（單檔案，適合超大資料庫）
• AUTOEXTEND：啟用自動擴展需指定 NEXT 增量
• MAXSIZE：限制最大擴展大小，UNLIMITED 代表無上限
• MINIMUM EXTENT：規範最小區塊大小，減少碎片
• EXTENT MANAGEMENT LOCAL：預設採用本地管理，效能優於字典管理
• UNIFORM SIZE：固定區塊大小，預設 1MB
• REUSE：檔案存在則覆寫，不存在則建立

五、刪除表空間指令對照

注意：指定 DATAFILES 時前方必須有 CONTENTS 關鍵字，否則報錯。

六、實戰案例演練

案例 1：建立雙檔案表空間並刪除

CREATE TABLESPACE exampletb
DATAFILE 'E:\examp01.dbf' SIZE 5M AUTOEXTEND ON NEXT 128K MAXSIZE 1000M,
         'E:\examp02.dbf' SIZE 5M AUTOEXTEND ON NEXT 128K MAXSIZE 1000M;

DROP TABLESPACE exampletb;

案例 2：建立表空間、建表、插入資料，再刪除表空間（含物件）

CREATE TABLESPACE exampletb
DATAFILE 'E:\examp01.dbf' SIZE 5M AUTOEXTEND ON NEXT 128K MAXSIZE 1000M REUSE,
         'E:\examp02.dbf' SIZE 5M AUTOEXTEND ON NEXT 128K MAXSIZE 1000M REUSE;

CREATE TABLE scott.student (
  ID NUMBER,
  NAME VARCHAR2(10)
) TABLESPACE exampletb;

DROP TABLESPACE exampletb INCLUDING CONTENTS;

說明：資料最終寫入 examp01.dbf、examp02.dbf 兩個檔案中。

案例 3：建立表空間並連同資料檔一併刪除

CREATE TABLESPACE exampletb
DATAFILE 'E:\examp01.dbf' SIZE 5M AUTOEXTEND ON NEXT 128K MAXSIZE 1000M REUSE,
         'E:\examp02.dbf' SIZE 5M AUTOEXTEND ON NEXT 128K MAXSIZE 1000M REUSE;

DROP TABLESPACE exampletb INCLUDING DATAFILES;

案例 4：跨表空間外鍵關聯下的級聯刪除

CREATE TABLESPACE exampletb1
DATAFILE 'E:\examp03.dbf' SIZE 5M AUTOEXTEND ON NEXT 128K MAXSIZE 1000M;

CREATE TABLESPACE exampletb2
DATAFILE 'E:\examp04.dbf' SIZE 5M AUTOEXTEND ON NEXT 128K MAXSIZE 1000M;

CREATE TABLE test1 (MOBILE NUMBER(13) PRIMARY KEY) TABLESPACE exampletb1;
CREATE TABLE test2 (MOBILE NUMBER(13) REFERENCES test1(MOBILE)) TABLESPACE exampletb2;

DROP TABLESPACE exampletb1 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

此指令會同時移除主鍵表空間、資料檔，並級聯刪除外鍵約束。

七、管理建議

• 生產環境建議啟用 AUTOEXTEND 並設定合理 MAXSIZE，避免空間耗盡導致業務中斷。
• 採用 LOCAL UNIFORM SIZE 管理區塊，減少碎片並提升分配效能。
• 定期監控 DBA_FREE_SPACE 與 DBA_DATA_FILES，提前規劃擴容。
• 刪除表空間前務必確認無關鍵業務物件，並做好備份。