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

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 ORCL_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;

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

1. Identity name & location of temp file in tablespace

SQL> select * from dba_temp_files where tablespace_name like ‘ORCL_TEMP';

2. Add additional temp file in temporary tablespace

SQL> ALTER TABLESPACE ORCL_TEMP ADD TEMPFILE ‘/u01/ app/oracle/ oradata/ iamdb/ dev_iastemp02.dbf’ size 1024M autoextend on next 10M maxsize 2000M;




SQL> create temporary tablespace TMPACCT2 tempfile '/u02/oradata/acct/tmpacct2-1.dbf'size 10M autoextend on next 10M maxsize 2000M;


SQL> alter user TCICDR TEMPORARY TABLESPACE TMPACCT2;







I hope you all have enjoyed reading this article. Comments are welcome....