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 Received on Thu Nov 09 2000 - 14:16:33 CST