Temp Tablespace error's and resolution

ora-01652

Troubleshooting Details
Error explanation
There is no enough space for a temporary segment. The size reported in the error message is the number of contiguous free Oracle blocks that cannot be found in the listed tablespace.

How to approach the ORA-1652 error
There are two views in the database that help keep track of the free space in the temporary tablespace: v$sort_usage and v$tempseg_usage (from 9i onwards).

In order for the approach to be relevant, the following investigation steps should be followed immediately after the error occurs : Check the status of the sort segment utilization :
select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS from v$sort_segment;
If USED_BLOCKS = TOTAL_BLOCKS, find which user and statement is using the temporary sort segment by following: (Note 317441.1 - How Do You Find Who And What SQL Is Using Temp Segments) In some cases, you may find that the ORA-1652 is not reported for a temporary tablespace, but a permanent one. This is not an abnormal behaviour and it can occur for example while creating or dropping objects like tables and indexes in permanent tablespaces. (Reference : Note 19047.1 - OERR: ORA 1652 unable to extend temp segment by %s in tablespace %s) In such cases the following note will be of use :
( Note 100492.1 - ORA-01652 : Estimate Space Needed to CREATE INDEX)

In the process of diagnostic and tuning, the resumable statement feature can be useful. It allows the DBA, once having applied the appropriate solution to the space allocation issue, to resume the suspended transaction which does not loose all the work done previously. By querying dba_resumable one can find the statement that is executed when ORA-1652 occurs. See Note 136941.1 - Using RESUMABLE Session to Avoid Transaction Abort Due to Space Errors.

There are two ways of solving this error

Add more tempfiles, increase the size of the current ones or enable auto extend and the most important:
Tune the queries/statements so that the sort operations are done in memory and not on the disk.
Note that the extents allocated for a user's sort segment are NOT deallocated but are marked as FREE from performance reasons. The FREE extents can be further used by other users that are executing sort
operations.  After the database restart the allocated extens are NOT released either but are FREE to be reused.
Hence, seeing  the physical space of the temporary tablespace fully allocated is not a reason to be concerned per se. The above query of V$SORT_SEGMENT should be used to establish the free space inside the temporary tablespace.

Known issues
Note 463819.1 - Database HANG After Migrating to 10.2 : ORA-1652
Note 164850.1 - ORA-01652 in Resumable Statements Prevents any SELECT on DBA_RESUMABLE View

SQL Queries

Temp Tablespace usage Report

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

Temp Space usage session wise

SELECT   S.sid || ',' || S.serial# sid_serial, S.username,
         T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
         T.sqladdr address, Q.hash_value, Q.sql_text
FROM     v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE    T.session_addr = S.saddr
AND      T.sqladdr = Q.address (+)
AND      T.tablespace = TBS.tablespace_name
--AND      T.blocks * TBS.block_size / 1024 / 1024  > 5024
ORDER BY S.sid

Regards
Manoj