Re: Question: Unusable indexes after tablespace move of table

BD, Oracle mentions the need to rebuild indexes after moving the table in the manuals, but does not automatically perform the rebuild for you.  Oracle leaves it to the DBA to determine when he or she want to perform this action, and to determine which method to use to rebuild the indexes: alter index rebuild, drop/recreate. I suggest that before using alter table move to rebuild/relocate a table you should check to see if each index tablespace has enough free space to allow you to rebuild each index on the table. If addequate free space does not exist you will have to use drop/recreate.

In either case you might also want to verify that no index over time has grown larger than your currently allocated temp tablespace can handle. If you are just using UFS files and have plenty of disk space this is probably not a problem. But if you use raw partitions or other file setups where the file space cannot be allocated on the fly then some advance work may be necessary.

HTH -- Mark D Powell -- Received on Thu Dec 29 2005 - 14:06:56 CST