Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: March 05, 2003 - 12:37 pm UTC

Last updated: January 14, 2008 - 3:32 pm UTC

Version: 9.0.1

Viewed 1000+ times

You Asked

Hi, Tom,

I got this error message when I tried to rebuild an index.
ORA-01652: unable to extend temp segment by 1024 in tablespace INDX
I have INDX tablespace to hold all indexes. INDX tablespace
is LMT, PERMENT, LOGGING.
I also have TEMP tablespace as default temporary tablespace.
I don't understand why Oracle is trying to extend temp segment on
a perment tablespace INDEX instead of TEMP where the sorting happens?

Thanks.

and Tom said...

Oracle will put the newly rebuilt index structure into a TEMP segment in the tablespace where the index is going to go. This is so that if the system just crashed right in the middle, when we restart -- SMON will be kind enough to seek out these orphan TEMP segments and get rid of them.

Upon completion of the index rebuild -- Oracle will convert this TEMP segment into a permanent segment, remove the old index segment and let this one take over.

So, this TEMP segment is really your newly rebuilt index as it is rebuilding.

You did not have enough space for both the old and new index in that tablespace is all.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Excellent !!

Raghu, January 12, 2008 - 8:48 pm UTC

Thomas, this solved my puzzle too. We had similar problems on 10202 during the usage of DBMS_REDEFINITION package. I overlooked at the entire error in the alert log and when isaw that it was unable to increase the TEMP segment, i thought we need more space on the TEMP tablespace. But we had failure again. I did online monitoring of the SORT_SEGMENT usage and it never hit the max limit and i was wondering what's the real issue. After reading this thread i relooked at the alert log and found that it was not the problem with the TEMP tablespace but with the INDEX tablespace itself. So i have few clarifications to get my concepts clear.

-- so this the case only incase of rebuilt ?
-- in case of new index creation/primary key creation, should i size the TEMP tablesapce to hold the sort data or the index tablespace ? I think its still the TEMP tablespace right ?

thanks in advance.

Raghu
Tom Kyte
January 14, 2008 - 3:32 pm UTC

this is the case of new segments being created.

a create table as select - or create index would do this.

You size temp for temporary table used to create the segment
the tablespace for the segment must be sized to hold the segment


basically, until the new segment is complete - we use "temporary extents" to hold it - so that a) you cannot SEE IT (it is not done) and b) if something fails, it just gets cleaned up naturally without any special work on our part.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.