表空間類型與建立語法
永久表空間
CREATE TABLESPACE data01
DATAFILE '/u01/oradata/db/data01.dbf' SIZE 500M
AUTOEXTEND ON NEXT 100M MAXSIZE 10G
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K -- 或 AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO; -- ASSM, 推薦
UNIFORM SIZE：固定區塊大小 (此例 128K)，預設 64KAUTOALLOCATE：系統自動管理區塊大小 (64K/1M/64M)ASSM (AUTO) 取代傳統 freelist，高併發效能更佳
UNDO 表空間
CREATE UNDO TABLESPACE undotbs02
DATAFILE '/u01/oradata/db/undotbs02.dbf' SIZE 500M
AUTOEXTEND ON NEXT 50M MAXSIZE 2G;
-- 切換啟用 (僅能有一個作用中)
ALTER SYSTEM SET undo_tablespace = undotbs02 SCOPE=BOTH;
暫存表空間
CREATE TEMPORARY TABLESPACE temp_data
TEMPFILE '/u01/oradata/db/temp_data.dbf' SIZE 200M
AUTOEXTEND ON NEXT 50M MAXSIZE 5G
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
表空間狀態控制
| 操作 | 語法 | 說明 |
|---|
| 離線 (正常) | ALTER TABLESPACE tbs OFFLINE NORMAL; | 檢查點完成後離線，無需恢復 |
| 離線 (需恢復) | ALER TABLESPACE tbs OFFLINE IMMEDIATE; | 檔案遺失時使用，上線需媒體恢復 |
| 上線 | ALTER TABLESPACE tbs ONLINE; | |
| 單一資料檔離線 | ALTER DATABASE DATAFILE 'path' OFFLINE; | 不影響同表空間其他檔案 |
| 唯讀 | ALTER TABLESPACE tbs READ ONLY; | 適合歷史歸檔、備份一致性 |
| 讀寫 | ALTER TABLESPACE tbs READ WRITE; | |
空間擴充三大招
1. 新增資料檔 (推薦，無停機)
ALTER TABLESPACE tbs
ADD DATAFILE '/u01/oradata/db/tbs02.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 10G;
2. 手動調整既有檔案大小
ALTER DATABASE DATAFILE '/u01/oradata/db/tbs.dbf' RESIZE 5G;
3. 啟用自動擴展
ALTER DATABASE DATAFILE '/u01/oradata/db/tbs.dbf'
AUTOEXTEND ON NEXT 200M MAXSIZE 10G;
物件遷移表空間
| 物件類型 | 語法 | 備註 |
|---|
| 表 | ALTER TABLE t MOVE TABLESPACE new_tbs; | 會導致 ROWID 變更，索引失效需 REBUILD |
| 索引 | ALTER INDEX idx REBUILD TABLESPACE new_tbs; | ONLINE 選項可避免鎖表 |
| LOB | ALTER TABLE t MOVE LOB(col) STORE AS (TABLESPACE new_tbs); | |
| 分區表 | ALTER TABLE t MOVE PARTITION p TABLESPACE new_tbs; | 逐分區遷移減少鎖定時間 |
刪除表空間
-- 僅刪除空表空間
DROP TABLESPACE tbs;
-- 含物件與資料檔 (謹慎!)
DROP TABLESPACE tbs INCLUDING CONTENTS AND DATAFILES;
監控查詢腳本
-- 表空間使用率總覽
SELECT t.tablespace_name,
ROUND(t.total_mb,2) total_mb,
ROUND(t.total_mb - NVL(f.free_mb,0),2) used_mb,
ROUND(NVL(f.free_mb,0),2) free_mb,
ROUND(100 * NVL(f.free_mb,0) / t.total_mb,2) pct_free
FROM (
SELECT tablespace_name, SUM(bytes)/1024/1024 total_mb
FROM dba_data_files GROUP BY tablespace_name
) t
LEFT JOIN (
SELECT tablespace_name, SUM(bytes)/1024/1024 free_mb
FROM dba_free_space GROUP BY tablespace_name
) f USING (tablespace_name)
ORDER BY pct_free;
-- 最大連續可用空間 (判斷能否滿足 NEXT EXTENT)
SELECT tablespace_name, MAX(bytes)/1024/1024 max_free_mb
FROM dba_free_space GROUP BY tablespace_name;
最佳實踐原則
- 採用 LMT + ASSM (LOCAL + AUTO)，避免 Dictionary Managed 與 Manual Segment Space
- 資料檔預留 AUTOEXTEND，MAXSIZE 設定上限防止撐爆磁碟
- 依業務分離表空間：DATA / INDEX / LOB / ARCHIVE / TEMP / UNDO
- 大表採用分區，分區級遷移/維護不影響整表
- 定期 COALESCE (僅 Dictionary Managed 需要) 或
ALTER TABLESPACE ... SHRINK SPACE (11g+)
- 監控告警閾值：Warning 75%、Critical 90%
