Oracle 空間管理實戰：表空間自由空間、擴展監控、碎片整理與連續空間分析

一、表空間自由空間監控

SELECT a.file_id "File#",
       a.tablespace_name "Tablespace",
       a.bytes "Total Bytes",
       a.bytes - SUM(NVL(b.bytes,0)) "Used Bytes",
       SUM(NVL(b.bytes,0)) "Free Bytes",
       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;

解讀指標

• %Free < 15%（高寫入表空間）→ 擴容或添加資料檔
• %Free > 80%（靜態表空間）→ 考慮縮減資料檔（ALTER DATABASE DATAFILE ... RESIZE）
• SYSTEM/TEMP 表空間長期高閒置 → 初始規劃過大，遷移重建時調整

空間釋放技巧

1. EXP/IMP 重組：導出→截斷→導入，合併碎片、回收高水位
2. 啟用 SEGMENT SPACE MANAGEMENT AUTO（LMT + ASSM）減少自由鏈表爭用

二、段擴展監控：預防 ORA-01628/01631/01632

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

處置策略

• Current Extents 接近 Max Extents：ALTER TABLE ... STORAGE (MAXEXTENTS UNLIMITED) 或增大 NEXT
• 大量小擴展（>50）：EXP/IMP 重組合併為大擴展，或 ALTER INDEX REBUILD

三、優化儲存參數：INITIAL/NEXT 調整實戰

1. EXP ... INDEXFILE=emp.sql 導出 DDL 腳本
2. 編輯 emp.sql 修改 STORAGE (INITIAL 10M NEXT 10M ...)
3. SQLPLUS @emp.sql 重建結構
4. 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) "Remaining"
FROM user_tables a, user_extents b
WHERE a.table_name = b.segment_name
GROUP BY a.table_name
ORDER BY 4;

Remaining < 10 觸發告警，預留擴容時間。

五、連續自由空間分析與合併

SELECT tablespace_name, file_id, block_id, bytes, blocks
FROM dba_free_space
WHERE tablespace_name = 'SFSJTS'
ORDER BY block_id;

連續性判斷

當前行 block_id + blocks = 下一行 block_id → 連續。
例：133719 + 126 = 133845 連續 256 blocks。

手動合併

ALTER TABLESPACE SFSJTS COALESCE;

SMON 會定期自動合併，但高碎片場景建議手動觸發或排程執行。

結語

空間管理三板斧：監控自由空間比率、控制擴展數量、合併碎片空間。將上述查詢封裝為每日巡檢報表，配合 DBMS_SPACE_ADMIN 自動化擴容/縮容策略，讓空間管理從「救火」轉為「預防醫學」。