Oracle 空間管理三大實戰技巧：表空間、擴展區、連續空間

技巧一：掌握表空間自由空間，動態調整配置

核心查詢：檔案級使用率

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;

判讀與行動準則

**快速釋放空間**：EXP 匯出 → DROP TABLE → IMP 匯入，可合併碎片、回收高水位以下空間。

技巧二：監控擴展區，防止過度擴展

定義：擴展區 > 5 視為過度擴展

SELECT SUBSTR(segment_name,1,15) "Segment",
       segment_type "Type",
       SUBSTR(tablespace_name,1,10) "TS",
       extents "Extents",
       max_extents "MaxExt"
FROM dba_segments
WHERE extents > 5 AND owner = 'JFCL'
ORDER BY segment_name;

處理策略

1. EXP/IMP 重建：匯出 → 刪除 → 匯入，將離散 Extent 合併為單一大 Extent。
2. 調整儲存參數：IMP INDEXFILE=emp.sql 產生 DDL → 編輯 INITIAL/NEXT/PCTINCREASE → 執行 DDL → IMP IGNORE=Y 導入資料。

預警：剩餘擴展區接近上限

SELECT a.table_name,
       MAX(a.max_extents) "MaxExt",
       MAX(b.extent_id)+1 "InUse",
       MAX(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
ORDER BY 4;

Unused 趨近 0 時需擴充 MAXEXTENTS 或轉用 LMT（Locally Managed Tablespace）消除上限。

技巧三：連續空間管理，消除碎片

查詢自由區塊連續性

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

判斷邏輯

若 BLOCK_ID + BLOCKS = 下一筆 BLOCK_ID → 兩區塊相鄰連續。
範例：第 2、3 筆 133719+126=133845 連續，合併 256 blocks。

手動合併指令

ALTER TABLESPACE tablespace_name COALESCE;

觸發 SMON 背景程序立即合併相鄰自由區塊（LMT 自動管理較少需手動）。

LMT vs DMT：現代空間管理

• LMT (Locally Managed Tablespace)：位圖管理 Extent，**預設、推薦**，自動追蹤自由空間、消除合併需求、支援均勻/自動配置大小。
• DMT (Dictionary Managed)：資料字典管理，易產生碎片、需手動 COALESCE，**已棄用**。

結語

空間管理三板斧：**監控使用率**（防滿）、**檢視擴展區**（防碎片/上限）、**合併連續空間**（提大Extent分配效率）。配合 LMT 自動管理、AUTOEXTEND、定期 EXP/IMP 整理，即可維持儲存層健康，避免 ORA-01653/01654 空間錯誤中斷業務。