Re: how determine unused space for indexes

Hi Mikael,

INDEX_STATS is your best bet and it contains all the statistics you're after. It only contains the latest analyze validate structure data so you'll need to write a script to dump the data to another table for each index.

Consider Locally Managed Tablespaces with smallish uniform extent size. Small indexes don't waste too much space, large indexes use many extents but performance should be hunky dory provided your not in the many 1000s (and even then you could be OK).

Good Luck

Richard
"Mikael Kruse" <mikael.kruse_at_ehs.ericsson.se> wrote in message news:ag43v0$ech$1_at_newstoo.ericsson.se...
> Hi,
>
> I'm suspecting that a database that I use for test could be created with a
> lot smaller extents than current settings. To investigate this, I want to
> find out how much of the space allocated for my indexes (many indexes)
> that's really used. I don't know if there's a High Water Mark for index
> segments as there is for table segments, but if there is, I want to know
how
> much of the space allocated that's below/above High Water Mark. Can anyone
> help me with this? I need a method that can be applied on many indexes,
> since I can't go through them one by one, there are far too many...
>
> Oracle version 8.0.5
>
> Thankful for assistance
> /Mikael
>
>
Received on Fri Jul 05 2002 - 08:33:22 CDT