Re: Need script to rebuild all indexes

kchriste_at_us.oracle.com (Kenneth E Christense) writes:

>kimmng_at_uswnvg.com (Kim Ng) writes:
 

>>Heikki Rissanen (rissahe1_at_news.ougf.fi) wrote:
>>: Andrew McConnell (mcconnel_at_source.asset.com) wrote:
 

>>: I haven't got what you want, but I've done this with v6 by taking the
>>: definitions by exporting without data. After that I did some editing
>>: of the exp-file to get the indexes ok and dropped and created them again.
 

>>: This is not the easy way, but it worked.
>>
>>Sounds like a lot of work. Why not write a SQL script that looks into
>>"all_indexes" and "all_tab_indexes" tables. They have all the latest
>>information you need. I don't have the script but it should be pretty
>>straightforward. I have done it before (a long time ago).
>>
>> -----------------------------
>> Kim Ng
>> (Just a low life contract programmer. Thus, my clients won't adopt my views.)
 

>Actually, not too much work. Just do a user exp, answer no to all prompts.
>Imp with 'indexfile=x.x' on command line.
 

>Your index create statements will be in file x.x. As a bonus, so will your
>table create statements, although they will be REMarked

The method one chooses would really depend on how many indexes you have; smaller the number export method is OK, if one needs to do it in future periodically the sql generated sql is better (and IMHO more elegant).

Problems I encountered with export method :

1. There are no line feeds in each sql statement so first vi (still use the old beast) complained about line too long and stopped at that line.
2. sqlplus has a limit on line length for sql statement, so I had to go back and put some (!) linefeeds (can someone tell me if this can be automated in vi).

After that it is easy breezy. This is real quick and dirty.

In case one decides to generate sql, set the line length and word wrap properly and the whole thing can be made to fly by wire.

Thanks for reading.

Happy computing.

Ajey Shahane                                            ashahane_at_netcom.com

____________________________________________________________________________