您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle TEMP 表空間滿載清理四大招：從重啟到診斷事件的終極解法

編輯：Oracle 資料庫基礎

現象與成因

正常情況下，排序、雜湊連線、建立索引等操作使用完 TEMP 表空間後，SMON 會自動釋放暫存段。但實務常遇到：暫存段未釋放、TEMP 近 100% 使用、甚至重啟資料庫仍無法釋放。主因通常為：大型排序超出 TEMP 空間、長時間交易持有暫存段、SMON 清理延遲、或暫存段標記異常 (ORA-600/ORA-7445 相關)。

四大清理方案 (由輕至重)

方案一：重啟資料庫 (最簡單、停機時間最長)

資料庫重啟時 SMON 會完整掃描並釋放所有暫存段。缺點：生產環境通常不允許隨意停機。

方案二：Metalink 官方建議 — 調整 STORAGE 觸發 SMON


ALTER TABLESPACE temp INCREASE 1;
ALTER TABLESPACE temp INCREASE 0;

透過微調表空間儲存參數，促使 SMON 重新檢視暫存段並嘗試釋放。無需停機，成功率中等。

方案三：精準殺 Session + COALESCE (筆者常用、風險可控)

找出佔用 TEMP 的 Session


SELECT s.username, s.sid, s.serial#, s.sql_address, s.machine, s.program,
       u.tablespace, u.segtype, u.contents
FROM v$session s, v$sort_usage u
WHERE s.saddr = u.session_addr;

殺掉持有暫存段的 Session
```
ALTER SYSTEM KILL SESSION 'sid,serial#';
```
注意：殺 Session 會回滾未提交交易，務必確認業務影響，必要時通知應用端。
COALESCE 整理 TEMP 空間
```
ALTER TABLESPACE TEMP COALESCE;
```
合併相鄰空閒區塊，釋放連續空間供後續使用。

方案四：診斷事件 — 「殺手鐧」 (無需殺 Session、無需停機)

確認 TEMP 表空間 TS#


SELECT ts#, name FROM sys.ts$;
-- 假設 TEMP 的 TS# = 3

執行即時清理事件


ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME DROP_SEGMENTS LEVEL 4';
-- LEVEL = TS# + 1 (即 3+1=4)

原理：觸發內部追蹤事件，直接要求 SMON 清理指定 TS# 的暫存段，效果即時且不影響其他 Session。

輔助監控：日常 TEMP 使用率查詢


SELECT UPPER(f.tablespace_name) "表空間名",
       d.tot_grootte_mb "表空間大小(M)",
       d.tot_grootte_mb - f.total_bytes "已使用(M)",
       TO_CHAR(ROUND((d.tot_grootte_mb - f.total_bytes)/d.tot_grootte_mb*100,2),'990.99') "使用%",
       f.total_bytes "空閒(M)",
       f.max_bytes "最大區塊(M)"
FROM (
  SELECT tablespace_name,
         ROUND(SUM(bytes)/1024/1024,2) total_bytes,
         ROUND(MAX(bytes)/1024/1024,2) max_bytes
  FROM sys.dba_free_space
  GROUP BY tablespace_name
) f,
(
  SELECT dd.tablespace_name,
         ROUND(SUM(dd.bytes)/1024/1024,2) tot_grootte_mb
  FROM sys.dba_data_files dd
  GROUP BY dd.tablespace_name
) d
WHERE d.tablespace_name = f.tablespace_name
ORDER BY 4 DESC;