Re: Rebuilding an index

> > My question is :
> >
> > How do I go about finding how big to make this first INITIAL extent ??
> > Are there any other implications, such as enough space in the TABLESPACE ??
> >
> > Thank you
> >
> > Carlos
>
> Why not just use: initial + ( extents - 1) * next
> from user_segments and user_indexes
>
> Voila !
> --
> Connor McDonald
> Systems Administrator-Unix/Oracle
> BHP Iron Ore
> "You're not drunk if you can lie on the floor without holding on"
> - Dean Martin

Two reasons why not:
* If pctincrease is > 0, the value of next will change to be the actual size of the next extent each time it extends (depending on which version of Oracle).
* Someone could have changed the next extent value (possibly many times). This is often done if the initial size estimates were way off and a teble starts getting much larger than expected.

The easiest way to find it's size is:
  select bytes from dba_segments where segment_name = 'name of index';

This will give you the largest this index has ever been. If you have purged a lot of data from the table, and don't plan on ever adding it back, you could reduce the size appropriately.

Some things to watch out for:
* You need the calculated amount of space to be contiguous if you want it to be one extent. This means that you might have to drop and re-create other objects in the tablespace that have fragmented the tablespace.
* Your extent size cannot be larger than your datafile size.

John Bechtel
Database Analyst
TDS Computing Services Received on Fri Mar 21 1997 - 00:00:00 CST