ORA-01652 (RAC)

ORA-01652 Unable to extend temp segment by XX in tablespace tbs.

In an RAC environment this error occurs in the following situation

Out of space in temporary tablespace.

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

If the query returns 0 then increase the size of the tablespace.

Error occurs even though space is available in the tablespace

This situation occurs when we are requesting the sort segments from another instance. This should be considered as a warning to the DBA that there is instance contention for temporary tablespace.

Use the following query to monitor the instance wise temp space usage.

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

Unlike non RAC environment, where the statement fails when this error occurs, the sort operations in RAC is successful.

Thanks