The quick fix for temp tablespace issue (ORA-1652) which is to add temporary datafile and then find root cause of temp tablespace issue.

Extent – An extent is a logical unit of database storage space allocation made up of a number of contiguous data blocks.One or more extents make up segment.
Segment – A segment is a set of extents that contains all the data for a specific logical storage structure within a tablespace.
Temporary Tablespace – contains transient data that persists only for the duration of the session.

Quick Fix for ORA-1652
=======================

1. Identify temporary datafile details :
SQL> select file_name , TABLESPACE_NAME from DBA_TEMP_FILES;
—–
/u01/oradata/VIS11i/temp01.dbf TEMP

2. Check if there is any space available in temporary tablespace (segment)

SQL> 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;

—-
output like

TABLESPACE MB_TOTAL MB_USED MB_FREE
——————————- ———- ———- ———-
TEMP 2548 2536 12

(in above case out of 2 GB only 12 MB is free)

3. Temporary fix

a) Resize temporary file as
SQL> ALTER DATABASE TEMPFILE ‘/u01/oradata/VIS11i/temp01.dbf’ RESIZE 3072M;

or

b) Add temp datafile to temporary tablespace as

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE ‘/u01/oradata/VIS11i/temp02.dbf’
SIZE 1024M REUSE AUTOEXTEND ON NEXT 50M MAXSIZE 1024M;

.

Root Cause Analysis
===================

1. Identify temp segment usages per session

–
– Temp segment usage per session.
–
SQL> 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 mb_used;

2. Identify temp segment usages per statement
–
– Temp segment usage per statement.
–
SQL> SELECT S.sid || ‘,’ || S.serial# sid_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.saddr
AND T.sqladdr = Q.address
AND T.tablespace = TBS.tablespace_name
ORDER BY mb_used;
Depending on outcome of temp segment usage per session and per statement focus on problematic session/statement.

.
References/Related

•793380.1 ORA-1652 Error Troubleshooting
•258941.1 RAC databases and ORA-1652: Unable To Extend Temp Segment By %s In Tablespace TEMP

=============================Real workaround========================

SQL> SELECT tablespace_name,SUM(bytes_used/1024/1024),SUM(bytes_free/1024/1024) FROM v$temp_space_header GROUP BY tablespace_name;

Tablespace SUM(BYTES_USED/1024/1024) SUM(BYTES_FREE/1024/1024)
——————– ————————- ————————-
GX_TEMP 84 2916
NAS30_TMP 196 804
PROSOD_TMP01 10592 37408
TEMP 5000 0
TIVOLI_TEMP 250 0

SQL> select bytes/1024/1024,FILE_NAME from DBA_TEMP_FILES where TABLESPACE_NAME=’TEMP’;

BYTES/1024/1024
—————
FILE_NAME
——————————————————————————————————————————————————
5000
/uat_u03/oradata/odrmu/data/temp_01.dbf

SQL> alter DATABASE TEMPFILE ‘/uat_u03/oradata/odrmu/data/temp_01.dbf’ resize 6000m;

Database altered.

SQL> select bytes/1024/1024,FILE_NAME from DBA_TEMP_FILES where TABLESPACE_NAME=’TEMP’;

BYTES/1024/1024
—————
FILE_NAME
——————————————————————————————————————————————————
6000
/uat_u03/oradata/odrmu/data/temp_01.dbf

SQL> SELECT tablespace_name, SUM (bytes_used/1024/1024),SUM (bytes_free/1024/1024) FROM v$temp_space_header GROUP BY tablespace_name;

Tablespace SUM(BYTES_USED/1024/1024) SUM(BYTES_FREE/1024/1024)
——————– ————————- ————————-
GX_TEMP 84 2916
NAS30_TMP 196 804
PROSOD_TMP01 10592 37408
TEMP 5000 1000
TIVOLI_TEMP 250 0