ORA-01652: Unable to Extend Temp Segment in RAC (Doc ID 1534590.1)

APPLIES TO:

Oracle Database - Enterprise Edition - Version 11.2.0.3 to 11.2.0.3 [Release 11.2]

Oracle Database Cloud Schema Service - Version N/A and later

Oracle Database Exadata Cloud Machine - Version N/A and later

Oracle Database Exadata Express Cloud Service - Version N/A and later

Oracle Cloud Infrastructure - Database Service - Version N/A and later

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.

REFERENCES

NOTE:14383007.8 - Bug 14383007 - Sort runs out of temp space in RAC even when temp space is available