Oracle 表空間與段擴展空間管理實戰技巧

DBA 日常需監控表空間剩餘空間、段擴展狀況，並掌握釋放碎片、調整儲存參數的技巧。本文提供實用查詢腳本與操作步驟，涵蓋自由空間分析、過度擴展偵測、Export/Import 重組、INITIAL 參數調整、擴展上限監控五大面向。

一、表空間自由空間監控

SELECT a.file_id "FileNo",
       a.tablespace_name "Tablespace",
       a.bytes "TotalBytes",
       a.bytes - SUM(NVL(b.bytes,0)) "Used",
       SUM(NVL(b.bytes,0)) "Free",
       ROUND(SUM(NVL(b.bytes,0))/a.bytes*100,2) "%Free"
FROM dba_data_files a, dba_free_space b
WHERE a.file_id = b.file_id(+)
GROUP BY a.tablespace_name, a.file_id, a.bytes
ORDER BY a.tablespace_name;

判讀準則：高頻寫入表空間 <15% 需擴充；靜態表空間 >20% 可考慮縮減；SYSTEM 表空間調整困難需謹慎規劃。

二、段過度擴展偵測與處理

SELECT SUBSTR(segment_name,1,15) seg_name,
       segment_type, SUBSTR(tablespace_name,1,10) ts_name,
       extents, max_extents
FROM dba_segments
WHERE extents > 5 AND owner = 'JFCL'
ORDER BY segment_name;

處理方式：EXPORT -> DROP -> IMPORT 可將離散區段合併為單一連續區段，重設 INITIAL/NEXT 參數。

三、調整表 INITIAL 儲存參數

1. EXP userid=scott/tiger file=emp.dmp indexfile=emp.sql 產生 DDL 腳本
2. 編輯 emp.sql，移除 REM 註解，修改 INITIAL 值
3. SQL> @emp.sql 重建表結構
4. IMP userid=scott/tiger IGNORE=Y file=emp.dmp 導入資料（IGNORE=Y 忽略建表錯誤）

四、監控段距離 MAXEXTENTS 餘額

SELECT a.table_name,
       MAX(a.max_extents) max_extents,
       MAX(b.extent_id)+1 in_use,
       MAX(a.max_extents) - (MAX(b.extent_id)+1) unuse
FROM user_tables a, user_extents b
WHERE a.table_name = b.segment_name
GROUP BY a.table_name
ORDER BY 4;

UNUSE 值過小（如 < 10）需提前規劃擴充或調整 MAXEXTENTS。

五、釋放碎片指令

ALTER TABLESPACE tablespace_name COALESCE;

合併相鄰自由區塊，獲得更大連續空間，減少區段分配失敗風險。