When Oracle throws the ORA-01652: unable to extend temp error, this may be an indication that your temporary
tablespace is too small. However, Oracle may throw that error if it runs out of space because of a one-time event, such
as a large index build. You’ll have to decide whether a one-time index build or a query that consumes large amounts
of sort space in the temporary tablespace warrants adding space.
To view the space a session is using in the temporary tablespace, run this query:
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;
If you determine
