How to fix unable to extend temp error

Sometimes you get ORA-01652: unable to extend temp error. This may be due to small temporary tablespace. you can execute the following query to find out the space a session is using in the temporary tablespace.

set linesize 1000
set pagesize 1000
col USERNAME format a20
col SPID format a10
col MODULE format a30
col PROGRAM format a30
SELECT s.sid, s.serial#, s.username
,p.spid, s.module, p.program
,SUM(su.blocks) * tbsp.block_size/1024/1024 mb_used
,su.tablespace
FROM v$sort_usage su
,v$session s
,dba_tablespaces tbsp
,v$process p
WHERE su.session_addr = s.saddr
AND su.tablespace = tbsp.tablespace_name
AND s.paddr = p.addr
GROUP BY
s.sid, s.serial#, s.username, s.osuser, p.spid, s.module,
p.program, tbsp.block_size, su.tablespace
ORDER BY s.sid;
you will find the temp tablespace consumption. You can either resize an existing datafile or add a new one.

To resize the temporary tablespace
SQL> alter database tempfile '/u01/oradata/SID/temp01.dbf' resize 2G;

To add a datafile to the temporary tablespace
SQL> alter tablespace temp add tempfile '/u01/oradata/SID/temp02.dbf' size 2G;