Re: Index reorganization

Well, here's one method and there are a few ways.. Assuming all indexes are owned by same account, run a user export without rows and indexes=y then on the import specify indexes=n and indexfile=run_ind.sql or whatever filename you wish to specify.

Next, drop all the indexes.. this following statement may help you to produce and drop index script...
select 'drop index '|| index_name ||';' from user_indexes; THEN
using sqlplus execute _at_run_ind.sql which is the output of the indexfile parameter from the import utility...edit it if you like to include unrecoverable parameters or modify the storage parameters. If there are some indexes you don't want to drop, don't drop them and the re-creation will just fail, as the object already exists..

BTW: why not rebuild the indexes to re-org, it would probably save you alot of headaches and you can also specify unrecoverable and storage parameters with the alter index rebuild statements....

Cheers,
Robert Prendin
Marco Mainini <mainini_at_realtech.de> wrote:

>Hi,
> Can anybody help me please.
>I'm a relatively newbie DBA and I've to reorganize a tablespace
>containing 3000 indexes. Which is the fastest way to accomplish the
>work? How can I drop and recreate all the indexes?
>Thanks for any help in advance.
>
>
>Marco
>
>==============================
> Marco Mainini
> mailto:mainini_at_realtech.de
>==============================
>
>

Robert Prendin
RP Data Solutions Inc.
Specializing in ORACLE DBA Support Services Received on Mon Nov 30 1998 - 00:00:00 CET