temporary tablespace usage in Oracle RAC

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.

temporary tablespace usage in oracle - find sessiosn performing sorting

Oracle provides two basic dynamic performance views for online monitoring of temporary tablespace usage. These are v$sort_usage to query online sessiosn using temporary segments and v$sort_segment to query the size of the temporary segements

V$SORT_USAGE has below main colums:

USERNAME – database user name
SESSION_ADDR – address of the session, can be used to identify the session in V$SESSION according to the SADDR column
SQL_ID – the identifier of the SQL that requires the sort or join, can be used to identify the SQL from V$SQL according to the SQL_ID column
EXTENTS – number of extents in the temporary segment being used by this session
BLOCKS – number of blocks in the temporary segment being used by this session

--based on v$sort_usage
--temp segment usage per session

SELECT S.sid || ‘,’ || S.serial# sid_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;

This sid and serial# can be used to find current query that is taking temp segment using view v$sqltext and passing sql_id of the sid and serial#

-- based on v$sort_segment
-- listing of temp segments

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;