11.2.0.3  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    

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.3 to 11.2.0.3 [Release 11.2] Information in this document applies to any platform.

Symptoms

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

 

Cause

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)/(1024*1024*1024),2) "Space(GB)" 2 from gv$sort_segment 3 where tablespace_name='XY_TEMP' 4 order by 1; INST_ID  TABLESPACE_NAME        Space(GB) ---------- ------------------------------ ---------- 1           XY_TEMP                           33.39 2           XY_TEMP                           33.77 3           XY_TEMP                           34.16 4           XY_TEMP                           33.39 5           XY_TEMP                           33.46 6           XY_TEMP                       1118.79   <<<<very unbalanced 7           XY_TEMP                           33.28 8           XY_TEMP                           34.26

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 11.2.0.4 (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;

Solution

Workaround is: 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 11.2.0.3 Description Temp space allocation fails with out-of-space ORA-1652 errors in RAC even when there is still free temp space available. Rediscovery Notes: User might hit this issue if temp space allocation fails with out-of-space in RAC even when there is still free temp space.