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