Oracle 空間管理實戰：表空間自由空間監控、擴展區偵測與自動化維護策略

一、表空間自由空間監控 — 精準掌握容量水位

核心查詢：檔案級使用率

SELECT a.file_id "File#",
       a.tablespace_name "Tablespace",
       ROUND(a.bytes/1024/1024,1) "Size_MB",
       ROUND((a.bytes - NVL(SUM(b.bytes),0))/1024/1024,1) "Used_MB",
       ROUND(NVL(SUM(b.bytes),0)/1024/1024,1) "Free_MB",
       ROUND(NVL(SUM(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, a.file_id;

解讀與行動閾值

**SYSTEM/SYSAUX/UNDO/TEMP** 空間管理策略不同：UNDO 依 UNDO_RETENTION 與 RETENTION GUARANTEE；TEMP 依排序/雜湊峰值；SYSTEM 極難縮減，建議預留 30%+。

二、擴展區偵測 — 避免 ORA-01631/01632 達到 MAXEXTENTS

查詢：超過 5 個 Extents 的段 (8i/9i 經驗值，LMT 環境下較不關鍵)

SELECT segment_name, segment_type, tablespace_name, extents, max_extents
FROM   dba_segments
WHERE  extents > 5
AND    owner = 'APP_USER'
ORDER  BY extents DESC;

現代 LMT (Locally Managed Tablespace) 觀點

• AUTOALLOCATE：Oracle 自動管理 Extent 大小 (64K→1M→64M)，**Extents 數量不再是問題**，關注 **總大小** 與 **碎片**。
• UNIFORM SIZE：固定 Extent 大小，Extents 多僅代表表大。
• **真正風險**：MAX_EXTENTS 達上限 (Dictionary Managed) 或 **檔案無法自動擴展** (Autoextend OFF / 達 Maxsize)。

三、碎片整理與空間回收

1. Export/Import (傳統重組)

exp system/manager file=ts_exp.dmp tablespaces=USERS
imp system/manager file=ts_exp.dmp tablespaces=USERS ignore=y
-- 整理行遷移、鏈接、釋放 High Water Mark 以下空間

2. Online Segment Shrink (10g+ 推薦)

-- 前提：ASSM 表空間、啟用 Row Movement
ALTER TABLE big_table ENABLE ROW MOVEMENT;
ALTER TABLE big_table SHRINK SPACE COMPACT;  -- 先壓縮 (不調整 HWM)
ALTER TABLE big_table SHRINK SPACE;        -- 再釋放空間
-- 索引同步重建
ALTER INDEX idx_big_table SHRINK SPACE;

3. 移動表/重建索引 (傳統、需鎖表)

ALTER TABLE big_table MOVE TABLESPACE users_new;
ALTER INDEX idx_big_table REBUILD ONLINE;

四、自動化維護策略

1. 監控腳本 (每小時跑一次，寫入歷史表/發送告警)

INSERT INTO ts_space_history
SELECT SYSTIMESTAMP, tablespace_name, file_id,
       ROUND(bytes/1024/1024,1),
       ROUND((bytes-NVL(free_bytes,0))/1024/1024,1),
       ROUND(NVL(free_bytes,0)/1024/1024,1),
       ROUND(NVL(free_bytes,0)/bytes*100,2)
FROM (
    SELECT a.tablespace_name, a.file_id, a.bytes,
           NVL(SUM(b.bytes),0) free_bytes
    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
);

2. Autoextend 策略

-- 資料檔啟用自動擴展，設定合理 MAXSIZE
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/PROD/users01.dbf'
   AUTOEXTEND ON NEXT 100M MAXSIZE 30G;

-- 監控 Autoextend 觸發頻率
SELECT file_name, autoextensible, maxbytes, bytes
FROM dba_data_files
WHERE autoextensible = 'YES';

3. 19c+ 自動化維護任務

-- 啟用自動段建議 (Segment Advisor)
SELECT * FROM dba_advisor_tasks WHERE task_name LIKE 'AUTO_SEG_ADV%';
-- 定期檢視建議報告
SELECT * FROM dba_advisor_findings WHERE task_name = 'AUTO_SEG_ADV_...';

結語

空間管理核心三步走：**監控水位 (Free%) → 預警擴容 (Autoextend/手動) → 定期整理 (Shrink/Reorg)**。LMT + ASSM + Autoextend + Segment Advisor 組合，**讓 95% 空間管理自動化**，DBA 僅需關注異常告警與長期容量規劃。建立 TS_SPACE_HISTORY 歷史表，支援趨勢分析與採購決策。