... 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
Title: RE: How do I find out the size of a index
You can also do:
analyze index INDEX_NAME validate structure;
select blocks, lf_blks, br_blks, btree_space, used_space from index_stats;
Alex.
-Original Message-
From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]]
Sent: Wednesday
Title: RE: How do I find out the size of a index
Roger,
Try DBA_EXTENTS instead. Something like below:
SELECT SUBSTR(TABLESPACE_NAME,1,16) Tablespace,
SUBSTR(OWNER,1,9) Owner,
SUBSTR(SEGMENT_NAME,1,30) Index,
SUBSTR(sum(BLOCKS * 8),1,16) Size K
FROM DBA_EXTENTS
WHERE
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
Use blocks or bytes dba_segments where segment_type = 'INDEX' or 'TABLE',
this shows how much a table or index has allocated space in datafiles.
Dba_tables blocks shows the number of blocks which are actually used inside
the segment and this number might be incorrect, if statistics on the table
is
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
