Oracle TEMP 表空間暴增至極限處理全攻略：查殺會話、縮減空間、參數調優

現象與影響

• TEMP 表空間持續增長至 MAXSIZE 或磁碟滿，導致 ORA-01652: unable to extend temp segment
• 排序、雜湊連線、建立索引、全域暫存表、資料庫鏈結遠端查詢等操作失敗
• 甚至拖垮整個資料庫實例回應

緊急處理三步驟

1. 鎖定佔用 TEMP 的會話與 SQL

-- 查詢正在使用暫存段的會話
SELECT s.username, s.sid, s.serial#, s.sql_id, s.machine, s.program, 
       su.tablespace, su.segtype, su.contents, su.blocks * 8192 / 1024 / 1024 AS used_mb
FROM   v$session s, v$sort_usage su
WHERE  s.saddr = su.session_addr
ORDER BY used_mb DESC;

-- 關聯 SQL 全文
SELECT sql_text FROM v$sql WHERE sql_id = '&sql_id';

2. 終止異常/失控會話 (謹慎操作，確認業務影響)

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
-- 或標記待終止 (交易回滾中)
ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;

3. 嘗試縮減 TEMP 表空間 (線上操作)

ALTER TABLESPACE temp COALESCE;  -- 整理碎片，釋放未使用區塊

-- 若 COALESCE 無效，嘗試 RESIZE 暫存檔 (需檔案有足夠空閒空間)
ALTER DATABASE TEMPFILE '/path/temp01.dbf' RESIZE 2G;

根治方案：徹底清理 Orphan Temporary Segments

當正常會話結束但暫存段未釋放 (如實例崩潰、強制關機、Bug)，需用內部事件清理：

-- 1. 確認 TEMP 表空間 ts#
SELECT ts#, name FROM sys.ts$ WHERE name = 'TEMP';
-- 假設 ts# = 3

-- 2. 執行清理事件 (需 SYSDBA，建議離峰時段)
ALTER SESSION SET EVENTS 'immediate trace name DROP_SEGMENTS level 4';
-- level = ts# + 1 = 4

注意：此操作會掃描並刪除該表空間所有無主暫存段，可能產生大量 REDO/UNDO，執行期間短暫影響效能。

參數調優：防止再次暴增

監控與預警儀表板

-- TEMP 使用率即時查詢
SELECT tablespace_name, 
       ROUND(used_space * 8192 / 1024 / 1024, 2) AS used_mb,
       ROUND(tablespace_size * 8192 / 1024 / 1024, 2) AS total_mb,
       ROUND(used_space / tablespace_size * 100, 1) AS pct_used
FROM   dba_temp_free_space;

-- 歷史趨勢 (需 AWR/ASH 授權)
SELECT * FROM dba_hist_temp_stat ORDER BY snap_id DESC;

• 設定告警：使用率 > 75% 發送郵件/簡訊
• 每日排程報表：Top 10 TEMP 消耗 SQL、會話、機器
• 定期 (月/季) 執行 COALESCE 並檢視暫存檔碎片率

常見誤區

• ❌ 以為 ALTER TABLESPACE temp SHRINK SPACE 可用 → 暫存表空間不支援 SHRINK
• ❌ 直接刪除暫存檔 → 會導致資料庫無法啟動
• ❌ 忽略 PGA 設定 → 所有排序/雜湊落盤，TEMP 必爆
• ✅ 正確：調大 PGA + 限制單一操作工作區 + 監控告警 + 定期清理

• 上一篇文章： 刀片伺服器發展 15 年回顧：四大誤區、技術轉折與可組合基礎設施新紀元
• 下一篇文章： 伺服器核心規格參數完整解讀：主頻、前端匯流排、快取、倍頻、位元寬度

關於伺服器

IIS 6.0 安裝與 .NET Framework 2.0 配置圖文完整教學

Windows Server 2003 校園 Web 伺服器常見問題與應用程式集區實戰

打造 Windows 2003 共享伺服器（二）：建立群組與分層資料夾權限