ORA-1652: unable to extend temp segment errors In RAC

 

ORA-1652: unable to extend temp segment errors In RAC

Error Description:
Error: ORA-1652 unable to extend temp segment by 64 in tablespace TEMP

Cause: Failed to allocate an extent for temp segment in tablespace.

Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated or create the object in another tablespace.

In a RAC enviornment, there are 2 scenerios where an ORA-1652 error can occur:

1. We are completely out of space in the temp tablespace.
2. Our local temp segment cannot extend but space for this temp tablespace is available on other instances.

To find out which scenerio we are hitting, run the following query:

select sum(free_blocks)
from gv$sort_segment
where tablespace_name = "tempTablespace";

If the free blocks is '0' then we have hit scenerio 1 and are completely out of temp space.
If sufficent space is available from the query, we are likely hitting scenerio 2.
If this happens, you may see ORA-1652 errors repeated in the alert log:
ORA-1652: unable to extend temp segment by 2048 in tablespace TEMP.

When this happens, we are requesting free space from another instance.
To see how space is allocated across all instances, run the following query:

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

The following are potential workarounds:
- Increase size of the temp tablespace
- Increase sort_area_size and/or pga_aggregate_target

參考文獻：
1. http://hi.baidu.com/wa0362/blog/item/6f203d6e5d050cdd80cb4ada.html
2.