Index build/rebuild without the Oracle 8i online clause creates your new index
as a temporary segment with name x.y where x is the file number and y is the rowid.
When done, Oracle locks, renames the temp segment to the name of the index,
drops the old index segment. You will run out of space if there aren't enough
chunks of free space of the proper size left for the new index/temporary segment.
You won't get the space taken up by the old index until the rebuild is over.
- Original Message -----
From: Shawn Ferris <Shawn.Ferris_at_twtelecom.com>
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
Sent: Thursday, November 09, 2000 2:27 PM
Subject: Index rebuilds and TEMP segments..
> 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).
Received on Thu Nov 09 2000 - 15:16:15 CST
