Oracle表空間管理三大核心技巧：空間監控、擴展優化與碎片整理

一、表空間自由空間監控與決策

透過查詢 DBA_DATA_FILES 與 DBA_FREE_SPACE 視圖，可精確掌握各表空間使用狀況：

SELECT a.file_id "FileNo",
       a.tablespace_name "Tablespace_Name",
       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;

空間管理決策準則

• 高插入/更新表空間：自由空間 < 15% 時需擴充
• 靜態資料表空間：自由空間 > 20% 可考慮縮減配置
• SYSTEM 表空間：調整困難，需重建資料庫
• 釋放空間技巧：Export/Import 可有效釋放碎片空間

二、表與索引擴展區監控與優化

識別過度擴展物件

擴展區超過 5 個視為過度擴展：

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

消除碎片的 Export/Import 重組法

1. Export 卸出表資料
2. Drop 原表
3. Import 重新載入，自動合併不連續區塊

調整儲存參數（以 INITIAL 為例）

1. imp userid=scott/tiger file=emp.dmp indexfile=emp.sql 產生 DDL 腳本
2. 編輯 emp.sql，移除 REM 註釋，修改 INITIAL 參數
3. SQL*Plus 執行 emp.sql 重建表結構
4. imp userid=scott/tiger ignore=y file=emp.dmp 載入資料（ignore=y 關鍵）

監控剩餘擴展配額

SELECT a.table_name "Table_Name",
       MAX(a.max_extents) "MaxExtents",
       MAX(b.extent_id)+1 "In_Use",
       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 值過小需立即處理擴充或調整 MAXEXTENTS。

三、連續空間碎片整理

識別連續自由空間

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

判斷邏輯：當前行的 BLOCK_ID + BLOCKS = 下一行 BLOCK_ID 表示連續空間。

手動合併碎片指令

ALTER TABLESPACE tablespace_name COALESCE;

執行後 SMON 會合併相鄰自由區塊，獲得更大連續空間，提升大型物件配置效率。