Re: How do I find out the size of a index

[Tim Gorman]

Title: Re: How do I find out the size of a index

Run ANALYZE INDEX <index-name> VALIDATE STRUCTURE and then query the session-level view INDEX_STATS.  Just be aware that this ANALYZE command puts a read-lock on the index, but it produces excellent information in INDEX_STATS...



on 8/13/03 4:49 PM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote:

... or even the actual size of the data

use dbms_space for that



DENNIS WILLIAMS <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED] 08/13/2003 10:39 AM
Please respond to ORACLE-L
       
        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
       cc:        
        Subject:        RE: How do I find out the size of a index


Roger - What do you mean by size? The allocation on disk, or the actual
number of blocks the index is using? Years ago when I was trying to get the
actual number of blocks an index was using (to predict when an index was
going to outgrow its extent), I used the LEAF_BLOCKS of the USER_INDEXES
table (after analyze, as Tanel points out. Theoretically this is slightly
under the total, but it was the closest I was able to find.

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED]


-----Original Message-----
Sent: Wednesday, August 13, 2003 11:54 AM
To: Multiple recipients of list ORACLE-L


Hi,

I can use "select blocks*8 from dba_tables where table_name='ABC'"
to find out the size of a table.
How do I find out the size of a index? I do not see a column BLOCK in
dba_indexes.

Thanks,

Roger Xu
Database Administrator
Dr Pepper Bottling Company of Texas
(972)721-8337