Re: Actual index sizes

Jonathan Lewis <Jonathan_at_jlcomp.demon.co.uk> wrote:

>In article <4apijg$1r1_at_cloner2.ix.netcom.com>
> chuckh_at_ix.netcom.com "Chuck Hamilton" writes:
 

>: Is there a way to find out exactly how much space an index is actually
>: using? What I want to do is find out if any of my index initial
>: segments are radically oversized, and if they are, I want to drop them
>: and recreate them at a more reasonable size.
>:
 

>Get the header_file number and header block number from dba_segments,
>then calculate (for Unix systems)
> header_block + power(2,26)*header_file
 

>then issue:
 

>alter session set events 'immediate name trace blockdump, level {number}';
 

>where {number} is the result from your calculation.

Thanks for the reply. Can you help further? When I tried this I got an error.

SEGMENT_NAME HEADER_BLOCK HEADER_FILE Calculation

----------------------- ------------ ----------- -----------
MEMBER_PK                      69542           8  536940454

SQL> alter session set events
  2 'immediate name trace blockdump, level 536940454'; alter session set events
*
ERROR at line 1:
ORA-02194: event specification syntax error 230 (minor error 211) near 'NAME' I'm using Oracle 7.1.6

--
Chuck Hamilton
chuckh_at_ix.netcom.com

Never share a foxhole with anyone braver than yourself!