Re: Maintenance on Indexes

Gillian <gmuruga_at_entergy.com> wrote:
> Is there a statement I can use to DROP and CREATE all simultaneously.
> There are about
> 12526 indexes. I could probably use the DBA_IND_COLUMNS table together
> with the
> DBA_INDEXES table.

It depends.

If you are using Oracle7, release 7.3, or Oracle8, you can use the command, "alter index ... rebuild". But this syntax does not exist in earlier releases.

I have created a small PL/SQL package which does nearly what you want (drops and then rebuilds all indexes on a given table ... of course you would have to call the package for each table), but of course it is owned by my employer and I cannot therefore post it or send it to you. (It is not a terribly difficult script to implement.)

I will say, though, that there are a couple of "gotchas" involved, expecially when dropping and rebuilding indexes that support PK and UK constraints -- you must first disable or drop foreign key constraints which depend on the PK/UK constraint, /then/ rebuild the PK/UK constraints, and then re-enable the FK constraints.

HTH

--
-bn