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

Share This Post with Your Friends over Social Media!

About the Author Atul Kumar

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

follow me on:

Leave a Comment:

1 comments
nisarg says February 17, 2015

Thanks a lot..It solved the issue.

Reply
Add Your Reply

[i]
[i]