DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2000
    Chester, England.

    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 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.

  2. #2
    Join Date
    Oct 2001
    My thought would be -

    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.

    Hope it helps,

    - Niranjan

  3. #3
    Join Date
    Sep 2002
    If individual datafiles are better - how do I build the indexes back into 5 or 6 100Mb datafiles like before.
    This can be done by simple creating your original tablespace and adding datafiles as necessary.

    Not a big fan of autoextend on, you could find youself in trouble if something goes wrong and your filesystem becomes full quickly

    Would also make the new tablespaces locally manage with uniform extents

  4. #4
    Join Date
    Feb 2000
    If tablespace name doesn't matter to you then you don't need to re-rebuild the indexes after rebuilding them once to the new tablespace.


  5. #5
    Join Date
    Jul 2000



    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.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.