Re: Maintenance on Indexes

Several of you on this thread correctly suggested to use

ALTER INDEX index_name REBUILD;

My research shows that if there is not enough space on the tablespace where the index is created,
then the index will be rebuilt and migrateed to the default tablespace of the user rebuilding the index.

I am not sure what Oracle uses to find this required space. It may be the contiguous intial extent for the index.

And I am not sure about this, but if there is not enough space on either tablespace, then index may
not be rebuilt.

The correct command to be used to rebuild the index on the desired tablespace is:

ALTER INDEX index_name REBUILD TABLESPACE tablespace_name;

Better yet, set the PCTINCREASE for the tablespaces in question to 1 be it for tables or indexes.
In this case the SMON process COALESCES the contiguous freespace. It may take SMON upto
to 15 to 20 minutes to do this. But it does the job and you do not need to

rebuild the indexes or even worry about the fragmented tablespace for tables.

suresh.bhat_at_mitchell-energy.com

Gillian <gmuruga_at_entergy.com> wrote in article <350E980F.6E456F8B_at_entergy.com>...
> I need to cut down the fragmentation on the indexes in Financials.
>
> 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.
>
> Let me know.
> Much appreciated.
>
> Gillian
> zmkj04_at_hotmail.com
>
>
Received on Wed Mar 25 1998 - 00:00:00 CST