Adding Temp File in Temporary tablespace ORA-01652: unable to extend temp segment by 128 in tablespace

.

I recently encountered issue ORA-01652 unable to extend temp segment by 128 in tablespace DEV_IAS_TEMP while upgrade of application which is self explanatory.

If you hit above error then check v$sort_segment

_______

SQL> SELECT TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS FROM V$SORT_SEGMENT;

 

Output in my case 

TABLESPACE_NAME                 TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS

——————————- ———— ———– ———–

TEMP                                    3584           0        3584
DEV_IAS_TEMP                          12672           0       12672

_________

 

To fix this issue, I added additional 1GB temp file in temporary tablespace reported in error (DEV_IAS_TEMP)

 

1. Identity name & location of temp file in tablespace

SQL> select * from dba_temp_files where tablespace_name like ‘DEV_IAS_TEMP’;

/u01/app/oracle/oradata/iamdb/dev_iastemp.dbf         13 DEV_IAS_TEMP                   104857600      12800 ONLINE            1 NO           0          0            0  103809024       12672

2. Add additional temp file in temporary tablespace

SQL> ALTER TABLESPACE DEV_IAS_TEMP ADD TEMPFILE ‘/u01/ app/oracle/ oradata/ iamdb/ dev_iastemp02.dbf’ size 1024m;

 

Related/References

  • 793380.1  ORA-1652 Error Troubleshooting
  • Data Blocks, Extents, Segments
  • 161357.1  ORA-1652 Out of Space Errors in the Databases TEMPORARY Tablespace

About the Author Atul Kumar

Oracle ACE, Author, Speaker and Founder of K21 Technologies & K21 Academy : Specialising in Design, Implement, and Trainings.

follow me on:

Leave a Comment:

2 comments
nisarg says February 17, 2015

Thanks a lot..It solved the issue.

Reply
Abhishek says November 15, 2017

Good one…important to note that table for temp datafiles is different from regular tablespaces

Reply
Add Your Reply

Not found