Oracle 空間管理實戰：表空間監控、區塊擴展優化與儲存參數調整

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

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;

輸出範例解讀

空間管理黃金法則

• 高 INSERT/UPDATE 活躍表空間：自由空間 < 15% 立即擴容
• 靜態資料表空間：自由空間 > 20% 可考慮縮減
• 利用 EXP/IMP 匯出匯入釋放碎片空間，延緩新增資料檔
• SYSTEM 表空間縮減極難，規劃時預留適度即可

二、表與索引擴展區監控：識別過度擴展

經驗值：Extents > 5 視為過度擴展，易導致效能下降與管理困難。

SELECT SUBSTR(segment_name,1,15) Segment_name,
       segment_type,
       SUBSTR(tablespace_name,1,10) Tablespace_name,
       extents,
       max_extents
FROM dba_segments
WHERE extents > 5 AND owner = 'JFCL'
ORDER BY segment_name;

輸出範例

發現問題立即處理：EXP 匯出 → DROP → IMP 匯入，將離散 Extents 合併為單一連續區段。

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

1. 匯出表結構產生 DDL 腳本：

   imp userid=scott/tiger file=emp.dmp indexfile=emp.sql

2. 編輯 emp.sql 修改 STORAGE 子句：

   CREATE TABLE "SCOTT"."EMP" (...)
   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
   STORAGE(INITIAL 10240 NEXT 10240 MINEXTENTS 1
           MAXEXTENTS 121 PCTINCREASE 50 FREELISTS 1)
   TABLESPACE "USER_DATA";

3. 調整建議：
   • PCTINCREASE = 0 固定擴展大小，避免幾何級數增長
   • INITIAL/NEXT 設為相同值，配合 LOCALLY MANAGED TABLESPACE 效果最佳
   • 大表預估最終大小，設定足夠 INITIAL 減少擴展次數
4. 重建表：DROP → 執行修改後的 emp.sql → IMP 匯入資料

四、現代最佳實務：本地管理表空間 (LMT) + 自動區段空間管理 (ASSM)

CREATE TABLESPACE users_data
DATAFILE '/u01/oradata/users_data01.dbf' SIZE 10G
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;

• LMT：位圖管理 Extents，消除字典爭用、支援自動/統一大小分配
• ASSM：位圖管理區塊自由空間，移除 PCTFREE/PCTUSED/FREELISTS 參數
• Oracle 9i+ 強烈建議全面採用 LMT + ASSM，簡化空間管理 90% 以上

空間管理的核心是「監控→預警→優化→自動化」。從手工監控 DBA_FREE_SPACE 到 LMT+ASSM 全自動管理，DBA 角色從「空間會計」轉型為「架構師」。