Oracle TEMP表空间占满：定位占用会话与SQL的排查处置

早上九点半，告警开始密集触发，一连串提示都指向同一件事：临时表空间使用率超过阈值，几笔跑批任务接连报出ORA-01652: unable to extend temp segment by ... in tablespace TEMP。业务方催得紧，DBA 群里最常见的第一反应是“赶紧加个 tempfile 顶一下”。加了以后确实恢复了，但过两周同一时间点又发生同样的问题，这说明问题从来不在 TEMP 表空间本身，而在往里面写数据的那批 SQL 和会话。Oracle 的 TEMP 表空间被占满这件事，排查难度不在命令有多复杂，而在信息分散：临时段视图、会话视图、SQL 执行计划、历史采样四拨数据要拼在一起才能定案。

这篇内容面向正在值班、或者正准备接手一套跑批系统的 DBA 和开发同学。我会从“临时段到底被谁吃了”讲起，一路拆到怎么把肇事 SQL 找出来、几种典型的占满形态、处置时哪些操作会出意外，最后是监控和根治的方向。里面不少是文档上不会写、只能在机房或值班群里学会的细节，比如DBA_TEMP_FREE_SPACE的强误导性、PX 从属进程的临时段怎么漏看、kill 完会话空间为什么不马上归还。

1. TEMP表空间被占满的真相：先分清“高水位”和“真占用”

很多团队在这个环节就判断错了方向，后面的动作全是无效的。我见过不止一次，值班同学贴出来一张截图，DBA_TEMP_FREE_SPACE里ALLOCATED_SPACE已经接近TABLESPACE_SIZE，于是判定“TEMP 满了”，直接开始加文件。实际上ALLOCATED_SPACE高只说明临时文件曾经被撑到过这个尺寸，跟“此刻有没有人占着”是两回事。

1.1 三个视图的语义差异决定了你看到的是不是真相

DBA_TEMP_FREE_SPACE只有四个字段：TABLESPACE_NAME、TABLESPACE_SIZE、ALLOCATED_SPACE、FREE_SPACE。判断的口径应该是看FREE_SPACE。当ALLOCATED_SPACE很高而FREE_SPACE同样很高时，真正的情况是临时文件被历史峰值撑大了、现在空着，这类属于容量浪费，不影响业务；只有当FREE_SPACE逼近 0，才是真的没地方分配新区。

SELECT tablespace_name, ROUND(tablespace_size/1024/1024/1024, 2) AS total_gb, ROUND(allocated_space/1024/1024/1024, 2) AS alloc_gb, ROUND(free_space/1024/1024/1024, 2) AS free_gb FROM dba_temp_free_space;

真正反映“谁在用、用了多少”的是V$TEMPGSEG_USAGE这个动态性能视图（在 RAC 下要换成GV$TEMPSEG_USAGE），它按临时段粒度列出当前分配情况。字段里几个关键的：SID、SERIAL#、SESSION_ADDR、SQL_ID、SEGTYPE、BLOCKS、CONTENTS。CONTENTS通常是TEMPORARY，SEGTYPE则告诉你这个段是干什么用的——SORT是排序落盘，DATA是全局临时表的数据段，HASH跟哈希连接或哈希分组相关，LOB_DATA和LOB_INDEX是临时 LOB。这一个字段就能把排查方向砍掉一大半。

还有一个很容易被忽略的维度：临时文件层面的容量。V$TEMPFILE里的BYTES、AUTOEXTENSIBLE、MAXBYTES决定了表空间的理论上限。遇到过好几次“我明明加了文件还是报 1652”，最后一查是这个 tempfile 的MAXBYTES卡在 2G，或者所在文件系统、ASM 磁盘组已经没余量了。

1.2 从等待事件倒推临时段的使用类型

不看视图，光看等待事件也能快速判断这类问题。会话在排序区放不下时会把中间结果写进临时段，事件表现为direct path write temp和direct path read temp，这一对事件出现在V$SESSION里，基本坐实了“排序或哈希溢出到临时表空间”。如果是多个会话抢临时表空间的区分配，会看到enq: TS - contention，这类监测的关键不在于空间够不够，而在于临时表空间的区管理出现争用，多个 tempfile 能分散一部分压力。

V$SORT_SEGMENT提供了一个表空间粒度的整体视图，CURRENT_USERS、USED_EXTENTS、FREE_EXTENTS能让你一眼看出当前有多少会话在用临时段、用了多少个区。它的局限是不带会话和 SQL 信息，需要和V$TEMPSEG_USAGE配合看。我在值班时通常的固定动线是：先DBA_TEMP_FREE_SPACE确认是不是真空了，再V$SORT_SEGMENT看用户数，然后才是V$TEMPSEG_USAGE找具体会话。三步走下来三十秒，比直接翻会话列表高效得多。

提示：临时表空间里不会有永久对象，但如果有人误建永久对象在 TEMP 里，CONTENTS会出现PERMANENT，这就不是容量问题了，属于设计错误，需要把对象迁走。

2. 精准定位占用TEMP的会话与SQL：把“谁在用”追到具体执行计划

定位到最后一定是“某条 SQL 在某次执行中吃了 40G 临时空间”。这一步的难点在于，V$TEMPSEG_USAGE里一个会话可能对应多个临时段，并行查询的场景下还会出现主会话（查询协调器）和一堆从属进程各自持有一段的情况，直接按行看会被数据淹没。

2.1 一条能直接抄的关联查询

按会话聚合是标准做法，SESSION_ADDR对应V$SESSION.SADDR：

SELECT s.sid, s.serial#, s.username, s.status, s.program, s.module, s.action, s.sql_id, s.event, COUNT(*) AS seg_cnt, ROUND(SUM(u.blocks) * 8192 / 1024 / 1024) AS temp_mb FROM v$tempseg_usage u JOIN v$session s ON s.saddr = u.session_addr GROUP BY s.sid, s.serial#, s.username, s.status, s.program, s.module, s.action, s.sql_id, s.event ORDER BY temp_mb DESC;

结果按temp_mb降序，前三名基本就是肇事者。RAC 环境把v$换成gv$，同时把inst_id加进SELECT和GROUP BY，因为临时表空间在实例间是各自独立的，只看单节点很容易得出“只有 3G，没那么严重”的错误结论。

拿到SID和SQL_ID之后，紧接着要看执行计划：

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', NULL, 'ALLSTATS LAST'));

重点盯三样东西：第一，有没有SORT ORDER BY、HASH JOIN、HASH GROUP BY出现在预估行数很大的位置；第二，E-Rows和A-Rows的偏差，偏差一到两个数量级就是统计信息过期或者绑定变量窥探导致计划劣化；第三，有没有PX相关操作，并行执行会让临时空间需求按并行度成倍放大。

2.2 会话已经结束怎么办：往历史里翻

现实里往往是等你登上主机，报错的会话早跑完了。这时候只能靠DBA_HIST_ACTIVE_SESS_HISTORY，它里面有TEMP_SPACE_ALLOCATED列，记录的是每次采样间隔内会话分配的临时空间量。按SQL_ID和时间窗口聚合，能大致还原是哪个 SQL 在某个时段把临时表空间撑起来的。

SELECT sql_id, TO_CHAR(sample_time, 'YYYY-MM-DD HH24') AS smp_hr, ROUND(SUM(temp_space_allocated)/1024/1024/1024, 2) AS alloc_gb FROM dba_hist_active_sess_history WHERE sample_time > SYSDATE - 1 AND temp_space_allocated > 0 GROUP BY sql_id, TO_CHAR(sample_time, 'YYYY-MM-DD HH24') ORDER BY alloc_gb DESC;

这段写法有个需要留意的地方：TEMP_SPACE_ALLOCATED是增量而非瞬时值，累加得到的是“累计分配量”，不等于峰值占用，别拿着一个 200G 的数字去找领导说“这条 SQL 用了 200G 临时空间”。要判断峰值，得按采样时刻看分布曲线。另外 ASH 有个保留下来的时间窗口，如果问题发生在一周前，且采样快照保留得短，这条数据可能已经找不到了，所以生产库上把 ASH 采样和 AWR 快照保留期设长一点是有代价但值得的。

2.3 三个导致定位失败的隐蔽因素

第一，SQL_ID为空。匿名 PL/SQL 块、动态 SQL 拼接、某些工具发起的批量操作，V$TEMPSEG_USAGE.SQL_ID可能取不到值。补位手段是PROGRAM、MODULE、MACHINE三件套，再配合V$SESSION的PREV_SQL_ID（最近一次执行过的 SQL）。

第二，模块信息缺失。连接池里的应用如果没做应用信息埋点，MODULE和ACTION全是空串，几百个会话看起来一模一样。我在几个项目里推动过这件事：在关键业务入口调用DBMS_APPLICATION_INFO.SET_MODULE和SET_ACTION打标记，工作量不大，但是把排障时间从两小时压到十分钟的量级。

第三，并行从属进程的干扰。并行查询在主会话之外会派生多个PX从属进程，它们各自在V$TEMPSEG_USAGE里出现，SQL_ID相同，SID不同。你在列表里看到二三十行几乎一样的记录别慌，这是同一个并行组的正常分布。kill 的时候也只 kill 主会话，从属进程会随之退出，逐个 kill 反而是给自己找麻烦。

3. TEMP表空间被撑爆的几种常见成因

临时表空间的占用上升都是业务行为的表现，绝不是“数据库自己在增长”。把历史上处理过的案例归类，占比最高的几种情况基本能覆盖八成以上。

3.1 执行计划劣化：统计信息过期与绑定变量窥探是主犯

一条本来跑得飞快的查询，突然开始吃几十 GB 临时空间，最常见的原因就是执行计划变了。典型路径是：业务表数据量从百万级涨到千万级，统计信息还是三个月前的，优化器以为某张表只有 5000 行，选了一个嵌套循环；后来统计信息被采集了，或者某个绑定变量值的分布特殊，计划切换到HASH JOIN——两个大表做哈希，构建侧整表塞进 PGA，放不下就往临时表空间溢出。

验证方式很直接，去V$SQL看同一个SQL_ID名下有没有多个PLAN_HASH_VALUE，以及各自的时间戳。

SELECT sql_id, plan_hash_value, child_number, executions, rows_processed, ROUND(SUM(temp_space_allocated_bytes)/1024/1024/1024, 2) AS temp_gb, last_active_time FROM v$sql WHERE sql_id = '&sql_id' ORDER BY last_active_time DESC;

以前出过一个印象深的案例：月末跑批，一条 Join 了七八张表的报表 SQL，平时占用 500MB 临时空间，某个月突然变成 90GB。查下来是其中一张维表新增了一个超高基数的字段，直方图还没跟上，优化器对过滤后的行数估算偏了三个数量级。处理方式并不复杂——抓取新统计信息，加一个组合索引，就回归正常了。

3.2 并行度失控：单条 SQL 吃掉整个实例的临时空间

并行查询对临时空间的放大是乘数级的。每个并行从属进程都会申请自己的排序区或哈希区，一个并行度 32 的并行查询，理论上能把临时空间需求放大到单进程的三十二倍。更麻烦的是并行度可能来自多个地方叠加：表上定义了PARALLEL 8、会话里ALTER SESSION FORCE PARALLEL QUERY、再加上PARALLEL_DEGREE_POLICY设了自动并行，最后生效的并行度谁都不清楚。

我的建议是先把实例级的并行从属进程总量卡住，PARALLEL_MAX_SERVERS按 CPU 核数的 2 到 4 倍设，再在业务跑批的会话级别上施加限制。跑批系统里出现“很多条 SQL 同时并行”是最糟糕的组合，等于所有 SQL 一起抢临时空间，谁都不够用。

3.3 全局临时表忘记清理，以及连接池的累计效应

全局临时表这条线特别容易被漏掉。ON COMMIT DELETE ROWS的 GTT 相对安全，事务提交后数据就清了；ON COMMIT PRESERVE ROWS的 GTT 要等到会话断开才释放临时段。应用侧如果用的是长连接或连接池，一个会话可能挂上好几天，GTT 里的数据一直不清，临时段也一直占着。

排查方式是从V$TEMPSEG_USAGE里筛出SEGTYPE = 'DATA'的记录，看它挂在哪些会话上、这些会话的LOGON_TIME多早、LAST_CALL_ET多长。如果发现几百个会话各自持有一段 GTT 的临时段，加起来就是一笔可观的数字。治本方式是让应用侧明确地在用完 GTT 后执行TRUNCATE TABLE gtt_name，或者把 GTT 改成ON COMMIT DELETE ROWS，或者把连接池的回收周期调短。

3.4 临时表空间使用量与维护任务的叠加

还有一类是周期性维护任务贡献的。索引REBUILD，尤其在线重建大索引，排序阶段会把临时表空间占掉一大块；DBMS_STATS采集统计信息、物化视图刷新、数据泵导出导入、大对象去重排序，都会在时间窗口内叠在一起。如果这些任务和业务跑批在同一个时间窗口，临时表空间的峰值就是两拨需求之和。

3.5 不要让这些操作“凑热闹”

有一类场景在 12c 之后的版本里变得很常见：临时 undo 相关配置生效后，临时对象产生的 undo 也会落到临时表空间，TEMP的使用量整体抬高一截。这种增量本身属于设计预期，并非泄漏，但很多人第一次遇到会误判成“临时表空间异常增长”，开始四处排查。判别方式是看V$TEMPGSEG_USAGE里的SEGTYPE，看看是否是这类系统内部段，而不是业务排序。

4. 处置动作的边界：从kill会话到收缩临时文件的完整操作细节

处置阶段最怕的是动作比问题还大。加临时文件、kill 会话、重建临时表空间这三档，风险依次递增，绝大多数情况下第一第二档就够了。

4.1 kill会话的正确姿势与它的副作用

定位到确切的SID和SERIAL#之后：

ALTER SYSTEM KILL SESSION '1234,56789' IMMEDIATE;

一定要带IMMEDIATE。不带的时候，如果会话正在执行一个需要回滚的事务，kill 只是打上标记，会话状态变成KILLED，实际占用要到很长一段时间后才归还，值班时看到“kill 了没用”基本都是这个原因。

带IMMEDIATE也不是瞬间完成的：事务回滚的时长取决于这次事务写了多少数据，几分钟到几十分钟都有可能。这个窗口里临时段还在，别急着做第二步动作。观察方式就是反复刷V$TEMPSEG_USAGE，行数降到零才算真正释放。

注意：kill 业务会话这件事一定要走流程确认。跑批任务被中断，恢复起来可能比重跑一次成本高得多。如果能等，优先选择等它跑完或者让它跑完再优化，别一上来就动刀。

4.2 扩容：加临时文件时那几条必须写的参数

临时文件扩容看起来最简单，问题是漏参数导致“加了没用”。

ALTER TABLESPACE temp ADD TEMPFILE '/path/to/temp02.dbf' SIZE 8G AUTOEXTEND ON NEXT 512M MAXSIZE 32G;

三个细节：MAXSIZE一定要写，不写的话默认是 32G 量级（不同平台版本有差异），大排序场景下很快就撞顶；NEXT不要设得太大，512M 到 1G 比较合适，设成 4G 的话每次扩展都会有一次明显的等待；文件路径要分散到不同的物理层面，全放同一个文件系统或者同一个 ASM 磁盘组，争用依然存在。

加完文件记得顺手确认一下是否生效：

SELECT file#, name, bytes/1024/1024/1024 AS gb, autoextensible, maxbytes/1024/1024/1024 AS max_gb FROM dba_temp_files;

RAC 环境下临时文件是每个实例私有的，每个节点都要单独确认，不要加了一个节点就以为全好了。

4.3 收缩：12.2 之后才有的省心选项

临时文件的特点是不能像普通数据文件那样随便 resize 缩小——只要还有活动临时段在上面，任何缩小操作都会失败。12.2 开始提供了专门的收缩语法：

ALTER TABLESPACE temp SHRINK SPACE; ALTER TABLESPACE temp SHRINK TEMPFILE '/path/to/temp01.dbf' KEEP 2G;

它的行为是尽量收缩，遇到正在使用的区跳过，所以是可以在线执行的低风险操作。更早的版本只能靠ALTER DATABASE TEMPFILE ... RESIZE，前提是新尺寸大于当前已用空间，否则报错。如果历史峰值远大于当前使用量，比如峰值 200G 而现在只用了 5G，早版本上只能重建临时表空间。

4.4 重建临时表空间的完整顺序与两个坑

重建是最后手段，标准步骤是：新建一个临时表空间，把数据库默认临时表空间切过去，然后回收旧的。

CREATE TEMPORARY TABLESPACE temp_new TEMPFILE '/path/to/temp_new01.dbf' SIZE 8G AUTOEXTEND ON NEXT 512M MAXSIZE 32G; ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_new;

切换完成后，现有会话不会自动迁移，新会话才会使用temp_new。所以千万别紧接着就DROP TABLESPACE temp，旧表空间上还挂着大量活动临时段，操作会被阻塞或者直接失败。正确的做法是等一个业务低峰期，确认V$TEMPSEG_USAGE里已经没有指向旧表空间的段，再删除。

第二个坑是忘了清理用户级的临时表空间指定。有些用户或角色上通过ALTER USER xxx TEMPORARY TABLESPACE temp显式指定过，切默认值对他们无效，新会话照样往旧的里面写。切换前先扫一遍：

SELECT username, temporary_tablespace FROM dba_users WHERE temporary_tablespace = 'TEMP';

这些用户要一并改过来，否则重建动作等于白做。

5. 把临时表空间的峰值压下来：三个层面的加固措施

处置完一次不等于结束，如果下个月同一个时间点再发生，说明根因没动。我一般从 SQL 层、参数层、监控层三处同时下手。

5.1 SQL层：能不进临时段的就别进

排序和哈希是临时空间的两大来源，减少它们的量是最本质的手段。几个具体方向：为高选择性查询补上覆盖索引，让ORDER BY能走索引有序读，直接省掉一次排序；把业务上其实不需要去重的UNION改成UNION ALL，UNION的隐式排序代价经常被低估；分页查询别用ORDER BY全表排序后再取前几十行，改成基于索引键的翻页；大表关联之前先把过滤条件做足，减少参与哈希构建的记录数。

还有一个容易被忽略的点：DISTINCT、GROUP BY、MINUS、INTERSECT这些写法的作用是确定的，但代价不一定被开发同学意识到。我通常会在 SQL 审核环节把“含排序类操作的语句必须附带执行计划的 A-Rows 评估”作为一条硬要求，尤其针对跑批类脚本。这一条比事后排查省事太多。

5.2 参数层：PGA 与并行的两个旋钮

自动 PGA 管理下，单个操作可用的工作区不是无限的，大致上有上限，超出部分就溢出到临时表空间。所以当PGA_AGGREGATE_TARGET设置明显低于实际工作集需求时，临时表空间压力会持续存在。调整 PGA 不是越大越好——它要和 SGA 抢物理内存，一般 OLTP 系统给 PGA 的份额在物理内存的 20% 到 30% 之间，跑批型系统可以适当提高。

并行这块，我会确认三件事：实例级PARALLEL_MAX_SERVERS是否合理；业务表上有没有人在建表时顺手加了PARALLEL而没人清理；跑批会话是否被强制开启了并行。把并行控制住，临时空间的峰值往往能直接降一半以上，比加文件的性价比高得多。

5.3 监控层：别等报错才知道

临时表空间这类问题的特点是毫无征兆地突然爆掉，所以必须提前埋点。我习惯用一个轻量的采样表配合定时任务做曲线记录，每五分钟采一次，把ALLOCATED_SPACE、FREE_SPACE、前几名会话和 SQL 一起落库。

CREATE TABLE mon_temp_usage ( sample_ts DATE, total_gb NUMBER, alloc_gb NUMBER, free_gb NUMBER, top_sid NUMBER, top_sql_id VARCHAR2(20), top_mb NUMBER );

采样可以用DBMS_SCHEDULER每五分钟跑一次存储过程，写入这张表；再用一个查询做阈值判断，连续三个采样点已用比例超过 70% 就发告警。告警内容一定要包含SQL_ID和MODULE，不然值班的同学接到告警还得从头查一遍。

我在几个跑批系统里用的阈值是：正常使用量低于 30%，日常波动到 50% 算正常，连续超过 70% 就该看一眼，超过 85% 直接通知。阈值不是拍脑袋定的，要结合至少一个月的采样数据，把月末跑批、月初结账这些峰值日期标出来，基线才准。

6. 我在实际处理中总结的几个关键经验

先说最容易踩的那个：看到ALLOCATED_SPACE接近上限就判断“满了”。临时文件的高水位只是历史峰值留下的痕迹，真正要看FREE_SPACE。我在不止一个环境里把它当成误报关掉了。后来改为告警规则只看FREE_SPACE占比，噪声直接降了九成。

第二，kill 完会话看到临时段还在就以为没生效。绝大多数是事务回滚还没完成，或者是并行从属进程还没退出。这时候最好的动作是什么都不做，等。反复 kill 会让会话状态更乱，尤其是KILLED状态的会话，反复提交 kill 并不会加速它。

第三，扩容语句漏MAXSIZE导致白干。最尴尬的一次是凌晨两点加了个 8G 的临时文件，第二天早上同一时间又报ORA-01652，查了半天才发现临时文件已经顶到默认上限，等于只顶了几个小时。

第四，并行从属进程的临时段在列表里占了很多行，看着像几十个会话在抢，其实是同一批。按SQL_ID聚合再看会清爽很多，也避免了误杀。

第五，统计信息过期这件事比想象中普遍。尤其那些每天都有大批量增删的表，统计信息的时效性直接影响执行计划稳定性。后来我把核心业务表的统计信息采集做成一键任务，跑批前必须执行，临时空间的异常波动少了大半。

第六，临时表空间的使用量在业务无障碍的情况下涨一截，先想想最近有没有改过版本、开过新特性、上过新模块。临时表空间使用量的变化，很多时候属于业务量自己变大了；它并不总意味着数据库出了故障。

再补一个小技巧：如果环境里应用没有做模块埋点，而你又想快速定位某个业务线是否在制造临时空间压力，可以先用V$ACTIVE_SESSION_HISTORY里的MACHINE和PROGRAM字段做分组统计，看哪些客户端机器贡献了最多的TEMP_SPACE_ALLOCATED。应用服务器一般是固定的几台，按 IP 一分，哪条业务线的问题基本就浮出来了，然后再针对性地去推动埋点改造。