220.127.116.11 RAC环境中， 可能由于节点 cache temp space，导致虽然free temp space还有很多，但仍有节点因为无法分配temp space而出现ORA-1652错误。
具体可以参考如下note， 目前已经提供了补丁Bug 14383007 – Sort runs out of temp space in RAC even when temp space is available
Oracle Database – Enterprise Edition – Version 18.104.22.168 to 22.214.171.124 [Release 11.2]
Information in this document applies to any platform.
Temporary tablespace space allocation fails in RAC even when there is still free temp space.
ORA-12801: error signaled in parallel query server P017
ORA-01652: unable to extend temp segment by 640 in tablespace XY_TEMP
Unbalanced temp space distribution in RAC. One instance seems to consume and cache most of the temp space, causing another instance to hit the ora-1652.
SQL> select inst_id, tablespace_name, round((total_blocks*8192)/(
2 from gv$sort_segment
3 where tablespace_name=’XY_TEMP’
4 order by 1;
INST_ID TABLESPACE_NAME Space(GB)
———- —————————— ———-
6 XY_TEMP 1118.79 <<<<very unbalanced
This is reported in Bug 14383007 – sort runs out of temp space on 2 nodes even when temp space is available
This bug will be fixed in 126.96.36.199 (future release). Refer < Document 14383007.8> for more details.
Useful queries for debugging:
Collect the information every few seconds:
1. select * from gv$sort_segment
2. select sum(bytes), owner from gv$temp_extent_map group by owner;
3. select inst_id, blocks_cached, blocks_used, extents_cached, extents_used from GV$TEMP_EXTENT_POOL;
Retry the operation.
One-off patch 14383007 has been provided for certain platform, please check My Oracle Support for patch detail.
Bug 14383007 Sort runs out of temp space in RAC even when temp space is available
Versions confirmed as being affected 188.8.131.52
Temp space allocation fails with out-of-space ORA-1652 errors in RAC even when
there is still free temp space available.
User might hit this issue if temp space allocation fails with out-of-space in
RAC even when there is still free temp space.