ORA-1652: unable to extend temp segment by 128 in tablespace

Overview:

The ORA-01652 error concerns a failure in allocating the extent for the temp segment in the tablespace. The temp segment refers to a temporary tablespace used internally by the Oracle database for the function of certain operations, such as joins.

The primary solution to an ORA-01652 error revolves around increasing the size of the temp tablespace. While you could create a new data file, it is recommended to just simply extend the existing space.

Temporary tablespaces are mainly used for sorting operations .Oracle do sorting in many cases like index creation, index rebuild, group by ,order by etc. If the amount of data is small Enough then entire sort will be completed in memory with no intermediate data written to disk . Each database user has a temporary tablespace (or temporary tablespace group in Oracle 10g) designated in their user definition. Whenever a sort operation grows too large to be performed entirely in memory, Oracle will allocate space in the temporary tablespace designated for the user performing the operation. After the database startup first sort operation that grow above memory will create a sort segment in the temporary tablespace and this segment will grow according to the sorting requirement. So there will be one sort segment for entire tablespace and Every session can share this one sort segment and this sort segment will be owned by sys.

When ever a session finishes the sorting then extents used by that session will be marked as unused and next session can make use of this extents for sorting operation.So we can say the Temporary segments are used in a rotating fashion .If a session error out or exit out unexpectedly SMON process will take the responsibility of cleaning temporary tablespace used by that Session .So smon will wake up every five minutes and start cleaning .So when user exit or terminated with unexpectedly and we need fasten cleanup of sort segment I will suggest shrink in that cases and in that case shrink is the best approach.But imagine the case where You have so many sort operation going on by different session on temporary tablespace and and there is no unused segments and there are no extents to extend the tablespace in that case addition of a tempfile will be the best option

Command :

Temp Tablespace Size:

set lines 180 pages 250
col FILE_NAME for a70
select file_name,bytes/1024/1024,autoextensible,MAXBYTES/1024/1024 from dba_temp_files where tablespace_name in ('&TBS');

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;

set lines 250
set pages 1000
col sql_id for a16
col sid for a10
col username for a10
col tablespace for a10
col hash_value for a25
col sid for 9999999
SELECT s.sid,s.sql_id, s.username, u.tablespace, s.sql_hash_value||'/'||u.sqlhash hash_value, u.segtype, u.contents, u.blocks
FROM v$session s, v$tempseg_usage u
WHERE s.saddr=u.session_addr
order by u.blocks;

Temp usage by Active sessions:

col username format a15
col osuser format a15
col program format a40
col machine format a40
set line 350
select s.osuser,
s.sid,
s.serial#,
p.spid "OS PID",
s.program,
s.machine,
s.process,
s.STATUS,
s.SQL_ID,
s.CURRENT_QUEUE_DURATION
from v$session s, v$process p Where s.paddr = p.addr and s.sid=&SID and s.STATUS='ACTIVE'
/

Monitoring Temp 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; 

Temp usage by sessions:

SELECT   S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
         S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
         COUNT(*) sort_ops
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,
         S.program, TBS.block_size, T.tablespace ORDER BY sid_serial;

Temp usage by sql statement:

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
ORDER BY S.sid;