ORA-1652: Unable to extend temp segment-RAC

Oracle Error:

ORA-01652: unable to extend temp segment by string in tablespace string 

Eg: ORA-1652: unable to extend temp segment by 128 in tablespace TEMP 

Cause:

In RAC environment, 2 reasons could cause this error.

Case-1:  No space available in the temp table space of which is being used.

Case-2: The second cause may have to do with the local temp segment not being able to extent space even though there is space in other instances.   

Action Plan:

In general, Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated. Normally, you would just add disk to TEMP to avoid the ORA-1652 error, but you can also wait for SMON to clean-up the TEMP segment.

CASE 1:

Find the free space in temp tablespace:

SQL> SELECT tablespace_name, file_name, bytes/1024/1024 size_in_mb from dba_temp_files;

SQL> select sum(free_blocks) from gv$sort_segment where tablespace_name = 'TEMP TABLESPACE NAME';

If the free block reads results '0', it signifies that there is no free space.

Work-around:

Add a new temp file to tablespace or resize the existing temp file.

To add a new file,

SQL> ALTER TABLESPACE tablespace_name ADD DATAFILE|TEMPFILE 'full path and file name' SIZE integer;      

To resize the existing temp file,

SQL> ALTER DATABASE DATAFILE|TEMPFILE ‘/u03/oradata/ temp02. dbf’ RESIZE 200M;

CASE 2:

In RAC, more sort segment space can be used from other instances, which can help resolve ORA-01652 more easily. 

SQL> select inst_id, tablespace_name, total_blocks, used_blocks, free_blocks from gv$sort_segment;

Where total_blocks & used_blocks ---> total temp segment space used &      

free_blocks ---> amount of space allocated to this particular instance.

If used_blocks = total_blocks and free_blocks = 0, it results in ORA-01652 error.

If ORA-01652 is causing the slowdown, SMON will probably not be able to process the sort segment requests. Try one of the following work-around:

1) Increase size of the temp tablespace

SQL> ALTER DATABASE DATAFILE|TEMPFILE ‘/u03/oradata/ userdata02. dbf’ RESIZE 200M;

2) Increase sort_area_size and/or pga_aggregate_target

SQL> alter system set sort_area_size = 32765 scope=both;

SQL> alter session set sort_area_size = 32765 (to modify in the current session itself)

SORT_AREA_SIZE specifies (in bytes) the maximum amount of memory Oracle will use for a sort. After the sort is complete, but before the rows are returned, Oracle releases all of the memory allocated for the sort, except the amount specified by the SORT_AREA_RETAINED_SIZE parameter. After the last row is returned, Oracle releases the remainder of the memory.

SQL> select inst_id, file_id, extents_cached, extents_used from gv$temp_extent_pool order by 1,2;

In RAC, extents are allocated from all temporary files uniformly. There are also changes to file header block during this operation. This is one of the reason, to create many temporary files in RAC. If you have 24 nodes in your RAC cluster, yes, that would imply that you would have to create 24 temp files to the TEMP table space.