{"id":6887,"date":"2014-02-01T18:04:33","date_gmt":"2014-02-01T22:04:33","guid":{"rendered":"http:\/\/onlineappsdba.com\/?p=6887"},"modified":"2014-02-01T19:33:12","modified_gmt":"2014-02-01T23:33:12","slug":"adding-temp-file-in-temporary-tablespace-ora-01652-unable-to-extend-temp-segment-by-128-in-tablespace","status":"publish","type":"post","link":"https:\/\/onlineappsdba.com\/index.php\/2014\/02\/01\/adding-temp-file-in-temporary-tablespace-ora-01652-unable-to-extend-temp-segment-by-128-in-tablespace\/","title":{"rendered":"Adding Temp File in Temporary tablespace ORA-01652: unable to extend temp segment by 128 in tablespace"},"content":{"rendered":"

.<\/p>\n

I recently encountered issue ORA-01652 unable to extend temp segment\u00a0by 128 in tablespace\u00a0DEV_IAS_TEMP<\/strong> while upgrade of application which is self explanatory.<\/p>\n

If you hit above error then check v$sort_segment<\/strong><\/p>\n

_______<\/p>\n

SQL> SELECT TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS FROM V$SORT_SEGMENT;<\/p>\n

 <\/p>\n

Output in my case\u00a0<\/strong><\/p>\n

TABLESPACE_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS <\/span><\/p>\n

——————————- ———— ———– ———– <\/span><\/p>\n

TEMP\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3584\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3584<\/span>
\nDEV_IAS_TEMP\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 12672\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 12672<\/span><\/p>\n

_________<\/p>\n

 <\/p>\n

To fix this issue, I added additional 1GB<\/strong> temp file in temporary tablespace reported in error (DEV_IAS_TEMP<\/strong>)<\/p>\n

 <\/p>\n

1.<\/strong> Identity name<\/strong> & location<\/strong> of temp file in tablespace<\/p>\n

SQL> select * from dba_temp_files where tablespace_name like ‘DEV_IAS_TEMP’;<\/strong><\/p>\n

\/u01\/app\/oracle\/oradata\/iamdb\/dev_iastemp.dbf<\/strong> \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 13 DEV_IAS_TEMP\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 104857600\u00a0\u00a0\u00a0\u00a0\u00a0 12800 ONLINE \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1 NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0 103809024\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 12672<\/p>\n

2.<\/strong> Add additional temp file in temporary tablespace<\/p>\n

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

 <\/p>\n

Related\/References<\/strong><\/span><\/p>\n