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

ORA-1652: unable to extend temp segment

Error

ADR Home = /u01/app/oracle/diag/rdbms/orcl/orcl1p:

*************************************************************************

2017-09-06 08:39:08.701000 +02:00

ORA-1652: unable to extend temp segment by 128 in tablespace TEMP [CITI]

ORA-1652: unable to extend temp segment by 128 in tablespace TEMP [CITI]

ORA-1652: unable to extend temp segment by 128 in tablespace TEMP [CITI]

Solution

You can resize the tempfile if it is restricted to limited size

SQL> alter database tempfile '/u01/app/oradata/temp01.dbf' resize 10240M

You can now configure the tempfile to grow up to certain size or up the maximum size allowed by oracle (32G)

 SQL> alter database tempfile '/u01/app/oradata/temp01.dbf'  autoextend on next 100m maxsize 20480;

or

SQL> alter database tempfile '/u01/app/oradata/temp01.dbf'  autoextend on next 100m maxsize unlimited;

If your tempfile already reached to maximum allowed file size limit then you must add new tempfile

Add tempfile in your temp tablespace to allow the TEMP tablespace to grow further

SQL> alter tablespace temp add tempfile   '/u01/app/oradata/temp02.dbf' size 10240M reuse autoextend on next 100m maxsize  unlimited;