ORA-1652: unable to extend temp segment by 128 in tablespace DBA_TEMP
| 方法 | 優點 | 缺點/限制 |
|---|---|---|
| 1. Event 1652 Trace | 自動生成 Trace 檔 | 僅對**未來**錯誤有效;可能影響效能;無法追溯歷史 |
| 2. V$SQL 排序 | 即時可查 | 難知執行時間點;罪魁 SQL 可能已 Age Out |
| 3. AWR/Statspack 報表 | 歷史視角 | 按總物理讀排序,低頻高耗 SQL 易被漏;不一定是 Temp I/O |
| 4. AWR 歷史視圖查詢 ⭐ 推薦 | 精準鎖定錯誤時間窗、直取 Direct Writes Top SQL | 需 10g+、Diagnostic Pack 授權 |
SELECT DISTINCT TO_CHAR(SUBSTR(b.sql_text, 1, 4000))
FROM sys.WRH$_SQLTEXT b
WHERE b.sql_id IN (
SELECT sql_id
FROM (
SELECT a.sql_id
FROM sys.WRH$_SQLSTAT a
WHERE a.parsing_schema_name NOT IN ('SYS')
AND a.executions_total > 0
AND a.direct_writes_total > 0
AND a.snap_id IN (
SELECT snap_id
FROM sys.WRM$_SNAPSHOT
WHERE TO_DATE('2008-08-20 17:20:08','YYYY-MM-DD HH24:MI:SS')
BETWEEN begin_interval_time AND end_interval_time
)
ORDER BY a.direct_writes_total / a.executions_total DESC
)
WHERE rownum <= 10
);
SELECT sql_text
FROM stats$sqltext
WHERE hash_value IN (
SELECT hash_value
FROM (SELECT hash_value
FROM stats$sql_summary
WHERE direct_writes > 0
AND snap_id IN (SELECT snap_id FROM stats$snapshot
WHERE snap_time BETWEEN ... )
ORDER BY direct_writes/executions DESC)
WHERE rownum <= 10
);
ORA-1652 看似空間不足,實則是 **「哪條 SQL 瘋狂吃 Temp」**。用 AWR 歷史視圖按 `DIRECT_WRITES/EXECUTIONS` 降序取 Top N,能在幾秒內鎖定元兇。建議將此查詢封裝為標準排查腳本,納入值班手冊。