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 = ''
If the free blocks is '0' then we have hit scenerio 1 and are completely
out of temp space. In this case see Note 19047.1 "OERR: ORA 1652 "unable
to extend temp segment by %s in tablespace %s" for instructions.
If sufficent space is available from the query, we are likely hitting
scenerio 2.
ORA-1652 errors are handled differently in a RAC envoronment than a non-RAC envoronment when the local instance cannot extend it's temp segment. In a non-RAC environment, an ORA-1652 can cause all SQL utilizing that tablespace to fail. In RAC, we may be able to get additional sort segment space from other instances.
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;
Total_blocks will show the total amount of temp segment space available for
each instance.
Used_Blocks will show how much of that space has been
utilized.
Free_blocks will show how much space has been ALLOCATED to this
instance.
If you are getting ORA-1652 errors on an instance, you will likely
see that used_blocks = total_blocks and free_blocks = 0. If this happens,
you may see ORA-1652 errors repeated in the alert log:
Sun Aug 1 08:12:41 2004
ORA-1652: unable to extend temp segment by 2048 in tablespace TEMP
Sun Aug 1 08:12:51 2004
ORA-1652: unable to extend temp segment by 2048 in tablespace TEMP
Sun Aug 1 08:16:37 2004
ORA-1652: unable to extend temp segment by 2048 in tablespace TEMP
When this happens, we are requesting free space from another instance. This should be considered a warning to the DBA that there is instance contention for temporary space. This may cause the instance to take longer to service internal temporary-space requests because inter-node coordination is
required.
Posts
