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:

Login to TOAD

Now open SCHEMA BROWESER

You can open SCHEMA BROWSER by two ways as illustrated below.

Now go to Tablespaces tab and from the list select the Tablespace name which needs to extend as illustrated below.

Now if you want to extend the existing datafile then click on “Edit Button”. Otherwise click on “ADD Button” (which is illustrated after edit).

You can see current size of “TEMP05” is 1 GB we are going to extend it to 2 GB as illustrated below.

In below picture you can see “TEMP05” is resized to 2 GB.  

If we have to add new datafile then click on “ADD Button” a small window “TEMP file definition” will appear now click on “Find/Copy” button to find the path one more small window “Select a file name” will appear click “OK” button it will bring us back to previous window “TEMP file definition” as illustrated below. 

Now change the file name and change the datafile size and set other options as per requirement and click OK.

Now you can see new datafile “TEMP05A” in the list. Now click OK. That’s it.

 

*************************************************END***********************************