Re: Index rebuilds and TEMP segments..

When Oracle rebuild the index, it will actually go out and get another extent and rebuild it there. Those extent(s) which Oracle get will mark as TEMP until it finish rebuild. Once the rebuilt is done, the status of the segment will turn from TEMP to index and the old index segments will be gone.

That is why you got this ORA-1652 error.

Winnie

• Original Message ----- To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Thursday, November 09, 2000 2:27 PM

> What am I missing:
>
> During a migration cycle, transforming legacy db's into the new app, the
> developers are rebuilding their indexes at strategic locations. (They
don't
> actually check whether it's necessary, they just do.) During the index
> rebuild, I believe they issue 'alter index ... rebuild ...', we get the
> following error:
>
> ORA-1652: unable to extend temp segment by 3200 in tablespace
DI_I
>
> DI_I is the tablespace that the indexes belong in. We've watched the temp
> segments come and go, in this tbsp. We watch the space as the rebuild
> happens and at minimum there is 1.5gb free. I'm assuming the storage for
> these segments are taken from the tbsp default and there is no way to
alter
> that behavior. The largest extent size is greater than the tbsp's default
> initial and next. (There is plenty of room for growth from what I can
tell.)
>
> (I'm completely guessing here.. Please correct me everywhere that I am
> wrong.) My suspicion is that as they rebuild these indexes, each time
they
> allocate new extents. Possibly these extents are marked for data use only
> and not useable for temp segments later. Each time they rebuild, they use
up
> all the space available to allocate for temp segments..
>
> If this is true, how do you alleviate this problem, given that I don't
have
> any control over the code. (third party) My thought was to try and
alternate
> between 2 tbsps, coalescing the last tbsp after all the objects move to
the
> new? (Which I would assume would mark them as useable space for any type
> segment.)
>
> Any help will be greatly appreciated!
>
> Shawn M Ferris
> Oracle DBA - Time Warner Telecom
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Shawn Ferris
> INET: Shawn.Ferris_at_twtelecom.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Tom Pall
  INET: tom_at_cdproc.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may