ORA-01652: unable to extend temp segment by 128 in tablespace TEMP2

CAUSE

DBA_TEMP_FREE_SPACE for tablespace_name = 'TEMP2' shows:

TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
TEMP2                          32212254720           1441792        32210812928

--although free space exists, still the temp segment cannot be extended

There is only one file (which is having .tmp extension) existing for the tablespace:

FILE_NAME                                                                        used MB Max MB AUTOEXTENSIBLE
/u01/data/UAT/datafile/o1_mf_temp2_c519yq1q_.tmp 30720       0     NO



If the database version is higher than 9i , then should have tablespace datafiles as .dbf, not as .tmp  So a new .dbf datafile needs to be added for the tablespace.


SOLUTION

 Add a new datafile for the tablespace either from command line:

ALTER TABLESPACE TEMP2 ADD TEMPFILE '/u01/data/UAT/datafile/temp02.dbf' SIZE 2000M;

Or by using Enterprise Manager Console.