3

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

Improve this question

1 Answer 1

Reset to default
4

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.

Improve this answer
1
  • -1 the error message clearly states that tablespace HFDORA_TEMP cannot be extended, so extend this tablespace.
    – miracle173
    Commented Oct 17, 2017 at 6:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.