ORA 01652 unable to extend temp segment

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

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

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

Cause: Failed to allocate an extent of the required number of blocks for a temporary segment in the tablespace indicated.

Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

For resolution you can use two methods by sql commands or through TOAD.

By using sql:

First of all check the status of your tablespace by using following queries.

SELECT tablespace_name, sum(bytes) free FROM DBA_FREE_SPACE

group by tablespace_name

order by tablespace_name

SELECT tablespace_name, round(sum(BYTES)/power(2,20)) TOTAL_MB

group by TABLESPACE_NAME

order by TABLESPACE_NAME

select * from dba_tablespaces where tablespace_name='TEMP05'

select * from dba_data_files where tablespace_name ='TEMP05'

select bytes/1024/1024 from dba_free_space where tablespace_name='TEMP05'

select bytes/1024/1024 mb from dba_TEMP_files where tablespace_name like'TEMP05'

select bytes/1024/1024/1024 gb from dba_TEMP_files where tablespace_name like'TEMP05'

Now extend or add new datafile to existing tablespace accordingly.

Extend tablespace:

ALTER DATABASE DATAFILE '<path_and_file_name>'

RESIZE <n>K|M|G|T|P|E;

e.g.

ALTER DATABASE DATAFILE '/u02/oracle/oradata/TEMP05.DBF'

RESIZE 1024 M

Add new datafile to existing tablespace:

ALTER TABLESPACE <tablespace_name>

ADD DATAFILE '<path_and_file_name>' SIZE <n>K|M|G|T|P|E;

ALTER TABLESPACE TEMP05 ADD TEMPFILE '/u02/oracle/oradata/TEMP05A.DBF' SIZE 1G AUTOEXTEND OFF

Where data file name must be change from the existing data file. Suppose if your TEMP05.DBF is full you should add new datafile by new name with existing file path TEMP05A.DBF.

By Using TOAD: