前言：表空間不足是 DBA 最常見的「半夜報警」
ORA-01650 (Rollback Segment) 與 ORA-01652 (Temporary Segment) 同屬「無法擴展 Segment」錯誤族，本質均為 **表空間可用連續空間耗盡**。本文深度解析兩錯誤的產生機制、診斷 SQL、臨時緩解與根治方案，並對比現代自動擴展/大文件表空間的最佳實踐。
一、ORA-01650: Unable to Extend Rollback Segment
1.1 錯誤全文
ORA-01650: unable to extend rollback segment string by string in tablespace string
Cause: Failed to allocate extent from the rollback segment in tablespace
Action: Use ALTER TABLESPACE ADD DATAFILE to add one or more files to the specified tablespace
1.2 產生機制
- 長事務 / 大批量 DML 產生大量 Undo 數據
- Undo 表空間 (Rollback Segment 所在) 可用空間不足、或因碎片無法分配連續 Extent
- 預設
PCT_INCREASE 導致下一 Extent 過大，超過剩餘最大塊
1.3 診斷 SQL 工具箱
-- 1. 查看表空間剩餘空間及最大連續塊
SELECT tablespace_name,
SUM(bytes)/1024/1024 AS total_mb,
MAX(bytes)/1024/1024 AS max_contiguous_mb,
COUNT(*) AS fragments
FROM dba_free_space
GROUP BY tablespace_name;
-- 2. 檢查 Rollback Segment 狀態
SELECT segment_name, tablespace_name, status, initial_extent, next_extent, min_extents, pct_increase
FROM dba_rollback_segs;
-- 3. 監控 Undo 爭用 (若任何 wait_class 佔比 > 1% 需擴展)
SELECT class, count
FROM v$waitstat
WHERE class IN ('system undo header','system undo block','undo header','undo block');
SELECT SUM(value)
FROM v$sysstat
WHERE name IN ('db block gets','consistent gets');
-- 計算: count / SUM(value) > 0.01 → 需增加 rollback segment
1.4 解決方案階梯
| 層級 | 動作 | 適用場景 | 風險 |
|---|
| 緊急臨時 | ALTER TABLESPACE undo_tbs ADD DATAFILE '/path/undo02.dbf' SIZE 2G AUTOEXTEND ON NEXT 100M MAXSIZE 10G; | 業務高峰期快速止血 | 依賴裸設備/文件系統剩餘空間 |
| 參數調優 | 調整 UNDO_RETENTION、UNDO_TABLESPACE、_UNDO_AUTOTUNE (隱含參數) | 長期優化，配合 AWR 分析 | 需測試驗證 |
| 架構升級 | 遷移至 **自動 Undo 管理** (10g+ 默認)，啟用 **保證 Undo 保留** RETENTION GUARANTEE | 新建庫/重構遷移 | 需規劃足夠空間 |
| 根治 | 大文件表空間 BIGFILE TABLESPACE + AUTOEXTEND ON MAXSIZE UNLIMITED + 監控告警 (閾值 80%) | 現代標準架構 | 單文件上限 128TB (32K block)，極難滿 |
二、ORA-01652: Unable to Extend Temp Segment
2.1 錯誤全文
ORA-01652: unable to extend temp segment by string in tablespace string
Cause: Oracle always tries to allocate contiguous space; fails when insufficient or fragmented
Action: Add datafile to tablespace, or increase PCT_INCREASE / reduce sort/join memory
2.2 產生場景
- 大排序 (
ORDER BY、GROUP BY、DISTINCT)、Hash Join、索引創建、物化視圖刷新
臨時表空間 (TEMP) 空間不足或碎片化嚴重
PCT_INCREASE 非 0 導致 Extent 指數增長，快速耗盡空間
2.3 診斷與緩解
-- 1. 檢查臨時表空間使用情況
SELECT tablespace_name,
SUM(bytes_used)/1024/1024 AS used_mb,
SUM(bytes_free)/1024/1024 AS free_mb,
ROUND(SUM(bytes_used)/SUM(bytes_alloc)*100,2) AS pct_used
FROM v$temp_space_header
GROUP BY tablespace_name;
-- 2. 查找佔用 Temp 空間最大的會話
SELECT s.sid, s.serial#, s.username, s.sql_id, u.tablespace, u.segtype, u.blocks*8192/1024/1024 AS mb
FROM v$session s
JOIN v$tempseg_usage u ON s.saddr = u.session_addr
ORDER BY u.blocks DESC;
-- 3. 臨時擴容
ALTER TABLESPACE temp ADD TEMPFILE '/path/temp02.dbf' SIZE 4G AUTOEXTEND ON NEXT 100M MAXSIZE 20G;
-- 4. 會話級調優 (避免大排序落盤)
ALTER SESSION SET workarea_size_policy = AUTO;
ALTER SESSION SET pga_aggregate_target = 500M; -- 或手動 sort_area_size/hash_area_size
2.4 現代最佳實踐：本地管理 + 統一大小 Extent + 自動擴展
-- 創建現代標準臨時表空間
CREATE BIGFILE TEMPORARY TABLESPACE temp
TEMPFILE '/u01/oradata/ORCL/temp01.dbf' SIZE 2G
AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
- LOCAL EXTENT MANAGEMENT：位圖管理，無字典爭用，高並發優異
- UNIFORM SIZE 1M：固定 Extent 大小，消除碎片、PCT_INCREASE 問題
- BIGFILE + AUTOEXTEND UNLIMITED：單文件 128TB 上限，運維極簡
三、通用表空間容量規劃與監控體系
| 維度 | 關鍵指標 | 告警閾值 | 自動化響應 |
|---|
| 永久表空間 | 已用%、最大連續空闲塊、增長趨勢 | 80% / 最大塊 < 100M | Autoextend / 增加 Datafile / 分區歸檔 |
| Undo 表空間 | 活躍事務數、Undo 保留時間、爭用比率 | 爭用 > 1%、保留時間不足 | 增加 Undo 文件 / 調大 RETENTION |
| 臨時表空間 | 使用率、活躍排序會話、最大排序空間 | 85%、單會話 > 2G | 增加 Tempfile / SQL 調優 (避免全表排序) |
| 系統表空間 | SYSTEM/SYSAUX 增長 | 90% | 移動 AWR/統計信息至 SYSAUX、定期清理 |
四、常見誤區辨析
| 誤區 | 真相 |
|---|
| 「加 Datafile 就能解決所有 1650/1652」 | 若磁盤無空間、文件系統 inode 滿、ASM 磁盤組滿，加文件同樣失敗。需全鏈路容量規劃。 |
| 「PCT_INCREASE 設置大點減少擴展次數」 | 指數增長反而加速空間耗盡並製造碎片。現代局部管理表空間**不使用 PCT_INCREASE**，改用 UNIFORM SIZE。 |
| 「Temp 表空間滿了重啟數據庫就好」 | 重啟僅釋放臨時段，根因 (大排序 SQL、PGA 太小、Temp 太小) 未解，必復發。 |
| 「Rollback Segment 過時概念，10g+ 不用管」 | 10g+ 雖默認自動 Undo 管理，但仍需規劃 Undo 表空間大小、保留時間、保證保留選項，否則長查詢報 ORA-01555 (Snapshot too old)。 |
結語
ORA-01650/01652 看似簡單的「空間不足」，背後折射的是 **容量規劃、Extent 管理策略、SQL 調優、監控體系** 的系統工程能力。從手工 ADD DATAFILE 到 **Bigfile + Local Uniform + Autoextend + 智能監控**，是每位 DBA 成長的必經之路。記住：空間告警 80% 介入，90% 升級，100% 故障。
