ORA-01652: unable to extend temp error, this may be an indication that your temporary tablespace is too small.

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

catupgrd.sql spooled file

In the case of a manual upgrade, were there errors reported in the spooled output of catupgrd.sql?

    No:
    -----
    Proceed to check 4.

    Yes:
    -----
    Work through the following checks

The following commands can be used to scan the output file for possible problems:

grep "PL/SQL" .log
grep -i "ora-" .log
grep -i "mgr-" .log
grep -i "oci-" .log
grep -i "sp2-" .log
grep -i "sp1-" .log
grep -i "sp3-" .log
grep -i "pls-" .log
grep -i error .log
grep -i severe .log
grep -i fatal .log
grep -i fail .log
grep -i stop .log

Or you can use the following command:

egrep -i '^pl/sql|^ora-|^mgr-|^oci-|^sp3-|^sp2-|^sp1-|^pls-|^error|^severe|^fatal|^fail|^stop' .log