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, Jared.Still_at_radisys.com at Jared.Still_at_radisys.com wrote:

>
> ... or even the actual size of the data
>
> use dbms_space for that
>
>
>
> DENNIS WILLIAMS <DWILLIAMS_at_lifetouch.com>
> Sent by: ml-errors_at_fatcity.com 08/13/2003 10:39 AM
> Please respond to ORACLE-L
>
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> 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.
> dwilliams_at_lifetouch.com
>
>
> -----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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Gorman
  INET: tim_at_sagelogix.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).