Oracle 19c中如何定位哪些SQL导致了临时表空间溢出？-Oracle-PHP中文网

v$sort_usage.sql_id实际对应会话的prev_sql_id而非当前sql，故常不准；应结合gv$active_session_history按temp_space_allocated增量定位真实sql，并检查视图/子查询隐式排序及执行计划细节。

直接查 v$sort_usage 得到的 sql_id 很可能不是真正肇事的 sql，它实际对应的是会话的 prev_sql_id —— 也就是上一条执行完的语句。如果会话后续又跑了别的 sql，这个字段就失效了。必须结合上下文交叉验证，否则优化方向全错。

为什么 `v$sort_usage.sql_id` 经常不准

Oracle 19c 中 v$sort_usage 的 sql_id 字段来源是 v$session.prev_sql_id，不是当前正在跑的 SQL。这意味着：

会话刚执行完一个大排序 SQL，紧接着执行了一个 SELECT 1 FROM DUAL，此时查 v$sort_usage 显示的仍是前一条 SQL 的 sql_id，但你看到的却是简单语句
prev_sql_id 在会话空闲或执行 PL/SQL 块时也可能被覆盖，尤其在应用使用连接池场景下更不可靠
v$tempseg_usage（19c 推荐视图）里字段名已改为 sql_id_tempseg，但它依然继承同一逻辑，不等于当前活跃 SQL

用 `gv$active_session_history` 追踪真实 SQL

这是最可靠的回溯方式，前提是问题发生时 ASH 正在采样（默认每秒一次，保留约 1 小时）。关键点在于按会话 + 时间窗口 + 临时空间增长量定位：

先从 gv$tempseg_usage 找出占用最大的 sid 和 serial#

再用这段 SQL 查该会话在出问题时间段内的 ASH 记录：

SELECT sample_time, sql_id, temp_space_allocated/1024/1024 AS mb, 
       temp_space_allocated - LAG(temp_space_allocated) OVER (ORDER BY sample_time) AS delta_mb
FROM gv$active_session_history 
WHERE session_id = &sid AND session_serial# = &serial#
  AND sample_time BETWEEN SYSDATE - 1/24 AND SYSDATE
ORDER BY delta_mb DESC;

重点关注 delta_mb 突增的那一行，对应的 sql_id 才是真凶
拿到 sql_id 后，立刻查执行计划：SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', NULL, 'ALLSTATS LAST'));，确认是否有 TempSpc 列且值巨大

捕获 ORA-01652 错误现场的 SQL

等报错再查太被动，但可以提前埋点：用事件跟踪让 Oracle 在每次触发 ORA-01652 时自动记录堆栈和当前 SQL。

Market Oracle

金融事件影响分析器 — 获取突发新闻，追踪金属/石油/加密货币/股票价格，预测短中长期市场连锁反应

下载

对特定会话启用：ALTER SESSION SET EVENTS '1652 trace name errorstack level 3';
或全局启用（谨慎）：ALTER SYSTEM SET EVENTS '1652 trace name errorstack level 3';

错误发生后，去 udump 目录找最新 trace 文件，里面会有类似这样的关键行：

Current SQL statement for this session:
select /*+ parallel(4) */ count(*) from big_table order by col1

注意：trace 文件只保留最近几次，需配合监控脚本定期归档或实时抓取

别漏掉视图和子查询里的“隐形 SQL”

很多溢出不是主 SQL 导致的，而是它调用的视图、物化 CTE 或嵌套子查询在内部做了全量排序或哈希连接。

查出的 sql_id 如果是 SELECT * FROM my_view，别急着优化这句——EXPLAIN PLAN 它，看执行计划里是否出现 VIEW 节点下挂了 SORT ORDER BY 或 HASH JOIN
特别检查视图定义中是否含 ORDER BY（Oracle 不下推）、DISTINCT、UNION ALL、多层 WITH 子句
用 DBMS_XPLAN.DISPLAY_CURSOR 的 'ADVANCED' 格式，看 Outline Data 部分有没有隐式物化提示（如 OPT_ESTIMATE(@"SEL$1", TABLE, "VW"@"SEL$1", SCALE_ROWS=1000)）
临时方案可加提示绕过：/*+ NO_MERGE(vw) */ 强制把视图当黑盒，避免优化器过度展开