Re: INDEX SPACE UTILIZATION

mkulkarn_at_na.stratus.com wrote:
>
> Is there a way to find how much of allocated space the indexes are
> really using. One way I have tried is to issue command "analyse index
> validate structure" and then query from index_stats table
> (btree_space,used_space and blocks). But for some reason this table
> holds only one row at a time. So when I analyse another index, the
> data for previous index is lost. I would like to write a script which
> will run every morning and give me list of indexes which have utilized
> more than 80% of the allocated space so that I can take appropriate
> action.

I wrote a PL/SQL procedure that uses the DBMS_DDL supplied package to analyze each index in "Estimate" mode. I then select the leaf_blocks column from user_indexes. Add one for the root block, and there you have it.

Send e-mail if you want more details.

-- 
Roger Harris
Oracle DBA
GE Credit Corp
Danbury, CT