ORA-01652 根因是 TEMP 表空间缺乏≥128个连续extent,非磁盘满;需查 DBA_TEMP_FREE_SPACE.free_space、临时文件可扩展性,杀残留会话并 COALESCE,根本解法是优化溢出 TEMP 的 SQL。

ORA-01652 不是磁盘满了,而是 TEMP 表空间里找不到 ≥128 个连续 extent 的空闲块——哪怕 DBA_TEMP_FREE_SPACE.FREE_SPACE 显示还有几百 MB,只要碎片化严重,照样报错。
查真实空闲空间,别信 DBA_FREE_SPACE
临时表空间不走 DBA_FREE_SPACE,查它会返回空或误导结果。必须用:
SELECT tablespace_name, free_space/1024/1024 AS free_mb, allocated_space/1024/1024 AS alloc_mb FROM DBA_TEMP_FREE_SPACE;
重点看 free_space 列(单位字节),不是百分比。如果接近 0,说明真没空间了。
同时确认文件是否可扩展:
SELECT file_name, autoextensible, maxbytes/1024/1024 AS max_mb FROM dba_temp_files;
- 若
autoextensible = 'NO'且free_mb极小,基本就是根因 - 若
maxbytes = 0,表示自动扩展被禁用,磁盘再空也扩不出去
优先加新临时文件,而不是扩旧文件
对在线业务来说,新增文件比 RESIZE 更安全、影响更小,且避免单文件 IO 瓶颈。
推荐命令:
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/oradata/db/temp02.dbf' SIZE 4G AUTOEXTEND ON NEXT 128M MAXSIZE 16G;
-
TEMPFILE不是DATAFILE,写错会直接报ORA-01119 - 严禁
MAXSIZE UNLIMITED:一个笛卡尔积 SQL 可能在几秒内打爆磁盘 - 路径必须是数据库能写的目录,且需提前在 OS 层创建好父目录
杀残留会话 + COALESCE 整理碎片
有时 FREE_SPACE 很小,但 dba_temp_files 文件大小并没满,说明有会话卡住没释放临时段。
先定位谁在占:
SELECT s.sid, s.username, u.tablespace, u.segtype, u.blocks*8/1024 AS mb_used FROM v$sort_usage u, v$session s WHERE u.session_addr = s.saddr ORDER BY u.blocks DESC;
- 对
BLOCKS持续不降、且USERNAME非空的会话,执行:ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE - 杀完立刻运行:
ALTER TABLESPACE TEMP COALESCE—— 它只合并相邻空闲区,不释放空间给 OS,但能快速腾出连续块 - 若报“表空间脱机”,说明某个
tempfile损坏,跳过此步,先加新文件恢复服务
根本解法:让 SQL 少用 TEMP
90% 的 ORA-01652 是少数几个 SQL 引起的,比如含 ORDER BY、GROUP BY、哈希连接或建索引的语句。盲目扩容只是掩盖问题。
优化方向:
- 用
EXPLAIN PLAN FOR查执行计划,重点看OPERATION是否含SORT ORDER BY或HASH JOIN,再核对BYTES列——若远超PGA_AGGREGATE_TARGET / 200,说明必溢出到 temp - 对已知大结果集的
ORDER BY,加提示走索引:/*+ INDEX(t1 idx_col) */ -
GROUP BY场景下,若分组键有窄字段索引,改写为:SELECT /*+ NO_SORT_GROUP_BY */ ... GROUP BY
临时段分配失败的本质,是内存不足 + 碎片共存;只看文件大小、不查会话和执行计划,永远在修水管而不关水龙头。











