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;

判讀指引

• Free% < 15% 且高 INSERT/UPDATE 活動 → 需擴充表空間。
• Free% > 20% 且靜態資料 → 可考慮縮減資料檔。
• SYSTEM 表空間縮減困難，需重建資料庫。
• 利用 EXP/IMP 匯出匯入可釋放碎片空間，延緩擴充需求。

二、段擴展區監控

A. 偵測過度擴展（Extents > 5）

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;

處理：EXP 匯出 → DROP → IMP 匯入，合併離散 Extent。

B. 調整 INITIAL 參數優化儲存

1. EXP 匯出表。
2. DROP 表。
3. IMP INDEXFILE=emp.sql 產生 DDL 腳本。
4. 編輯 emp.sql 修改 INITIAL。
5. 執行 emp.sql 重建表。
6. IMP IGNORE=Y 匯入資料。

C. 監控距離 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) unused
FROM   user_tables a, user_extents b
WHERE  a.table_name = b.segment_name
GROUP BY a.table_name
ORDER BY 4;

UNUSED 過小需關注調整。

三、連續自由空間分析

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

判斷連續：block_id + blocks = 下一行 block_id 即為連續。例：133719+126=133845 連續 256 blocks。

四、手動合併自由空間

ALTER TABLESPACE tablespace_name COALESCE;

SMON 會定期自動合併，手動執行可立即獲得大塊連續空間。