Rebuilding Index Tablespace Strategy
My index tablespace has been growing for 2 years. I do check it and rebuild individual indexes as and when, generally reducing their size by around 2-5%.
The datafiles have autoextend OFF. So when the Tablespace reaches between 97-99% full, I add another datafile.
I now have 7 x 100Mb Index datafiles. They are all DICTIONARY Managed - this is a legacy of the original pre-Ora8i database design.
We now run Ora 8.1.5.0 on NT4.
I want to completely rebuild these indexes - all of them.
I was planning to create a REBUILD_INDEX tablespace with 1 datafile - of say 250Mb - with AUTOEXTEND ON.
Then I was going to rebuild ALL the indexes into it.
Then I was going to drop and recreate the original tablespace.
Then I was going to REBUILD all the indexes back into this tablespace.
I do like the existing individual 100Mb datafiles we use as its a good graphic visualisation of the growing tablespace. Is it best to build them all back into just 1 datafile? if so, what happens if the 1 big datafile becomes corrupt? Wouldn't it be better to use several smaller datafiles (For recovery purposes)?
If individual datafiles are better - how do I build the indexes back into 5 or 6 100Mb datafiles like before.
Is there a better way to accomplish what I'm trying to achieve?
Advice - comments - criticism all welcome.