Bugs in Temp segments in RAC

Bug 2934117 : ORA-1652:Can’t acquire free extents owned by other node.The scripts fail as a result of the error

Solution: Do not use default temporary tablespace for a RAC configuration.
Workaround
Step1: Create a small temporary tablespace def_temp.
Step2: Set it as default temporary tablespace.
Step3: Create other normal temporary tablespace temp.
Step4: For all users change the temporary tablespace to temp.
Step5: Shutdown immediate all instances.
Step6: Startup all instances
Step7: when new users are created set the temporary tablespace as temp.


Bug 2858082 : Even if the free extents are available, ORA-1652 occurs in Alert Log on RAC. The scripts do not fail but errors appear inside alert.log of RAC instances.

In this bug although scripts that need Temp tablespace space due to Sort Operations (order by, group by, join, etc) are successfull we may see this error message inside alert.log of RAC instances.

When a RAC instance fills its own sort pool of extents, instead of allocation new extents it may ask for free temp extents from the other RAC instances. During this operation the ORA-1652 may appear inside the alert.log although the operation has succeeded.

Solution: You may ignore this error.

Thanks

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

ORA-01652 (Non RAC)

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

The space in the temporary tablespace is not enough to allocate for the temp segment.

A temp segment, in the temporary tablespace, is not always used for sorting. It is sometimes used for creating objects such as index, CTAS etc. At the end of the creation these temp segments are converted in to the real objects. Hence rebuilding/creating a large index requires large temp segments thereby resulting in ORA-01652.

Solution: Increase the size of the temp tablespace by using the statement

SQL> Alter tablespace add tempfile ‘path’ size ‘size’;

Thanks

Monitor Temporary Tablespace

To monitor the temporary Segment Usage

SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
( SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size ) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

To monitor the temporary storage usage per session

SELECT S.sid , S.serial#, S.username, S.osuser, P.spid,
S.module, P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used,
T.tablespace, COUNT(*) statements
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
P.program, TBS.block_size, T.tablespace
ORDER BY sid, serial#;

To monitor the Temporary storage usage per statement

SELECT S.sid , S.serial#, S.username, Q.hash_value, Q.sql_text,
T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace
FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE T.session_addr = S.saddrAND T.sqladdr = Q.address
AND T.tablespace = TBS.tablespace_name
ORDER BY S.sid;

Thanks