如何解决Oracle存储过程调用导致的Temp表空间占用过高问题？-Oracle-PHP中文网

Temp表空间爆满时SELECT卡住需查V$SORT_SEGMENT，确认未释放临时段；检查AUTOCOMMIT、GTTS提交选项、DBMS_SQL游标关闭、UNIFORM碎片、CTE嵌套及并发段归属。

Temp表空间爆满时，`SELECT` 语句卡住但没报错？先查`V$SORT_SEGMENT`

oracle里temp空间撑爆，往往不是存储过程本身写得错，而是它触发的排序、哈希连接或全局临时表（gtts）没释放。最典型的现象是：存储过程执行完，select 查询变慢甚至挂起，dba_temp_free_space 显示空闲为0，但v$session里找不到明显活跃会话。

关键动作是立刻查V$SORT_SEGMENT：

SELECT tablespace_name, segfile#, segblk#, blocks, extents FROM V$SORT_SEGMENT;

如果BLOCKS持续不归零，说明有事务未提交或会话异常断开但临时段没回收——Oracle不会自动清理断连会话遗留的Temp段，尤其在应用层用连接池且未正确关闭游标时。

确认是否所有调用该存储过程的应用都设置了AUTOCOMMIT=ON（Java JDBC需显式设conn.setAutoCommit(true)）
检查存储过程中是否用了CREATE GLOBAL TEMPORARY TABLE但没配ON COMMIT DELETE ROWS（默认是ON COMMIT PRESERVE ROWS，会撑满Temp直到会话结束）
避免在存储过程中嵌套大量ORDER BY + LIMIT（12c+）或ROWNUM分页，这类操作极易触发大排序

`DBMS_SQL` 动态执行导致Temp泄漏？别让`OPEN_CURSOR`裸奔

用DBMS_SQL拼SQL并PARSE/EXECUTE时，如果没配DBMS_SQL.CLOSE_CURSOR，游标长期占用会导致临时段无法释放——这不是内存泄漏，而是Oracle把动态SQL的中间结果缓存在Temp里，直到游标关闭。

常见错误写法：

cid := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cid, 'SELECT * FROM ... ORDER BY col', DBMS_SQL.NATIVE);
-- 忘了 DBMS_SQL.CLOSE_CURSOR(cid);

必须确保每个OPEN_CURSOR都有对应CLOSE_CURSOR，且放在EXCEPTION块里：

用PRAGMA AUTONOMOUS_TRANSACTION包裹的子过程里调用DBMS_SQL，更要手动关游标（自治事务不继承父事务上下文）
DBMS_SQL.EXECUTE_AND_FETCH内部也会建临时结构，不能只关PARSE游标，要等FETCH完再关
若用DBMS_SQL.VARCHAR2_TABLE批量绑定，绑定数组过大（如>1000行）会触发哈希分组，直接占Temp

临时表空间配了`UNIFORM SIZE`但还是碎片化？换`SYSTEM`管理方式

Oracle 11g+ 默认用SYSTEM管理Temp表空间（即自动段空间管理），但老系统常被手工改成UNIFORM SIZE（比如统一设成128M）。问题在于：当一个查询需要130M临时空间，UNIFORM SIZE=128M就会分配两个区（256M），多出的126M一直被锁住无法复用，久而久之形成“假满”——DBA_TEMP_FREE_SPACE显示有空间，但实际无法分配新区。

QuantOracle

63个确定性量化金融计算器 + 10个通过MCP的复合工作流。期权定价、Greeks、奇异衍生品、风险指标、投资组合优化……

下载

解决方法不是扩大小，而是切回SYSTEM管理：

ALTER TABLESPACE temp TEMPFILE '/path/to/temp01.dbf' DROP INCLUDING DATAFILES;
CREATE TEMPORARY TABLESPACE temp 
  TEMPFILE '/path/to/temp01.dbf' SIZE 1G 
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M; -- 错！
-- 正确：
CREATE TEMPORARY TABLESPACE temp 
  TEMPFILE '/path/to/temp01.dbf' SIZE 1G 
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

AUTOALLOCATE允许Oracle按需分配64K/1M/64M等不同大小区，大幅降低碎片
已有UNIFORM表空间不能直接改，必须重建；重建前确保无活跃会话使用该Temp表空间（查V$SORT_USAGE）
别给Temp表空间加MAXSIZE UNLIMITED——失控增长比碎片更危险

存储过程里用`WITH`子句嵌套太深？拆成物化中间结果

Oracle对WITH子句（CTE）的优化有限，尤其是嵌套4层以上+含ORDER BY或GROUP BY时，容易反复落盘到Temp。现象是V$SQL_PLAN里看到大量TEMP TABLE TRANSFORMATION和SORT AGGREGATE操作。

实操建议优先降级处理：

把最内层稳定数据抽成GLOBAL TEMPORARY TABLE，显式INSERT /*+ APPEND */并加索引（GTTS上可建索引）
用/*+ MATERIALIZE */提示强制物化某一层CTE，但仅限12c+，且要配合RESULT_CACHE开关（RESULT_CACHE_MODE = FORCE）才生效
避免在CTE里写SELECT * FROM (SELECT ... ORDER BY x) WHERE ROWNUM ——这会让整个子查询先排序再截断，Temp用量翻倍

真正难缠的不是语法写法，而是Temp段归属不清：同一个存储过程被多个并发调用时，每个会话的临时段独立存在，但DBA很难快速定位哪个会话拖着10GB没释放——所以监控必须落到V$TEMPSEG_USAGE + SID + SQL_ID三字段联查，而不是只盯总用量。