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

I have increased temp tablespace temp02.dbf by the following command

alter tablespace HFDORA_TEMP add TEMPFILE '/orac/u01/oradata/hfd2/system/hfd2/temp02.dbf' size 2000M;

But after increasing it same error is coming. Kindly suggest.


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

Error message ORA-01652 means: you don't have enough free temp space (enough free memory inside temp tablespace) or local temp segment not being able to extent space even though there is space in other instances.

To troubleshoot this issue: execute the following commands:

select TEMPORARY_TABLESPACE from dba_users where USERNAME = 'myuser'

then take the result from first SQL command to help you get free space inside that tablespace

select sum(free_blocks)
from gv$sort_segment
where tablespace_name = 'result'

If you get the result 0 thats mean the temp tablespace doesn't have enough free space, otherwise you need to check segment (mostly happened for RAC) by executing the following command:

select inst_id, tablespace_name, total_blocks, used_blocks, free_blocks
from gv$sort_segment;

check total_blocks and used block and adjust the segment as needed.

StackExchange Database Administrators Q#132253, answer score: 4