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

ora-01652: unable to extend temp segment by 128

Error occurred due to temp table-space is full. You need to add new temp file in your tablespace.

Solution
Following are the steps to add temp file in the TEMPORARY tablespace.

1. Check the temp file location and tablespace name for temporary tablespace.

COL TABLESPACE_NAME FOR A10
COL FILE_NAME FOR A40
SELECT TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024/1024 "ALLOCGB",MAXBYTES/1024/1024/1024 "MAXSIZE" FROM DBA_TEMP_FILES;

TABLESPACE FILE_NAME                         ALLOCGB MAXSIZE
---------- --------------------------------- ------- -------
TEMP       E:\ORACLE\ORADATA\ORCL\TEMP01.DBF      16      16
TEMP       E:\ORACLE\ORADATA\ORCL\TEMP02.DBF      16      16

2. Check the space on your operating system.

3. You have two option.
3.1. Add new Temp file in that location with same configuration.

ALTER TABLESPACE TEMP ADD TEMPFILE 'E:\ORACLE\ORADATA\ORCL\TEMP03.DBF' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 16G;

Note: Check the tablespace name and datafile location before adding it in database.

3.2 If your Max size of existing file is less than 32 GB then you can increase the file size upto 32GB limit.
Note: In my example i show you the size extend to 25 GB.

ALTER DATABASE TEMPFILE 'E:\ORACLE\ORADATA\ORCL\TEMP01.DBF' AUTOEXTEND ON MAXSIZE 25G;

ALTER DATABASE TEMPFILE 'E:\ORACLE\ORADATA\ORCL\TEMP02.DBF' AUTOEXTEND ON MAXSIZE 25G;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.