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 220.127.116.11 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?
1) keeping multiple datafiles will make sense only if you could span these files across several disks. this will give faster disk i/o.
2) Keeping them in separate datafiles or single datafile will hardly make a difference in case of corruption as it is recommended to take the complete index tablespace offline and recover. As partial availability leads to strange problems.
The number of datafiles is not a big issue if you have a lot of datafiles you only have to change maxdatafiles and/or db_files once and a while.
My advice would be give all indexes the same extent size (and pct inc = 0) or better use a LM tablespace with uniform size.
This would enhance the rebuild which can be done in the tablespace without causing fragmentation.