深度解析 Oracle 空間管理：表空間、區塊、延伸區與碎片整理

DBA 需透過資料字典掌握空間使用狀況，適時調整。本文提供實用查詢腳本與操作指南。

1. 表空間自由空間監控

SELECT a.file_id "FileNo",
       a.tablespace_name "Tablespace",
       a.bytes "TotalBytes",
       a.bytes - SUM(NVL(b.bytes,0)) "UsedBytes",
       SUM(NVL(b.bytes,0)) "FreeBytes",
       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%（高 INSERT/UPDATE 表空間） → 需擴充。
• 自由空間 > 20%（靜態表空間） → 可考慮縮減。
• 利用 EXP/IMP 或 Data Pump 匯出匯入可釋放碎片空間。

2. 延伸區過多物件偵測與整理

延伸區 > 5 視為過度擴展：

SELECT segment_name, segment_type, tablespace_name, extents, max_extents
FROM   dba_segments
WHERE  extents > 5 AND owner = 'SCOTT'
ORDER  BY segment_name;

整理方法：EXP → DROP → IMP 將不連續區塊合併；或使用 ALTER TABLE ... MOVE + ALTER INDEX ... REBUILD（10g+ 支援 ONLINE）。

3. 調整 INITIAL/NEXT 等儲存參數

1. EXP ... INDEXFILE=emp.sql 產生建表腳本。
2. 編輯 emp.sql 修改 STORAGE (INITIAL ...)。
3. @emp.sql 建立新表結構。
4. IMP ... IGNORE=Y 匯入資料。

4. 監控 MAXEXTENTS 逼近情況

SELECT a.table_name, a.max_extents,
       MAX(b.extent_id)+1 "InUse",
       a.max_extents - (MAX(b.extent_id)+1) "Unused"
FROM   user_tables a, user_extents b
WHERE  a.table_name = b.segment_name
GROUP  BY a.table_name, a.max_extents
HAVING a.max_extents - (MAX(b.extent_id)+1) < 10
ORDER  BY 4;

5. 連續自由空間檢查與合併

SELECT * FROM dba_free_space WHERE tablespace_name = 'USERS' ORDER BY block_id;

相鄰區塊判斷：BLOCK_ID + BLOCKS = 下一筆 BLOCK_ID 即為連續。
手動合併：ALTER TABLESPACE users COALESCE;（SMON 也會定期自動合併）。

空間管理不是一次性動作，而是持續監控與微調的過程。建立定期報表，趨勢化管理最有效。