Re: Alter index rebuild script

You do not alter index. You just drop and recreate one. If you already have one , there is no reason why you won't be able to drop and recreate an index. If you are running Oracle7.2 or later version, I suggest you to use UNRECOVERABLE option to speed up the index recreation.

If you are using any CASE tool, it is very easy to reverse engineer an index from the database and generate a script, you could also build one by select all of the necessary information from USER_INDEXES and UNSER_IND_COLUMNS. If you do not have much experience doing all of the above, or simply don't want to take the risk, I suggest the following :

• Before dropping the index, export the table With Constraints, and Without data (esp. if the table is too big to finish the export quickly).
• Run import with INDEXFILE option. This will get you the index creation script on a flat file, and save you lot of headache.
• Make necessary modification in the script such as adjsting SIZing parameters (INITIAL NEXT extent etc.), UNRECOVERABLE option,TABLESPACE etc.
• Add DROP Index statements before every CREATE INDEX statement in the script. * Make sure that nobody is using the TABLE at the time when you are ready to REORG the index. * Run the script (do not forget to spool the whole thing in a log file in order to find out if everything ran ok.

Good Luck (You may need some of it!).

Umesh
In article <883398228.144834568_at_dejanews.com>,   FRANCIS.ANSAH_at_ntc.nokia.com wrote:
>
> Hi,
> Please, could anyone help me with the alter index rebuild command.
>
> 1) I would like to know how it renames the old index and what happens
> if the index is dropped?
>
> 2) A script to demostrate how the rebuild index grows.
>
> Help for the beginner is ignorant. Thanks
>
> -------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet