Re: Actual index sizes

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.

This will result in a trace file that holds a symbolic block dump of the index's header block.

Lurking in all the mess will be some short code which identify:

	number of extents				nex
	current extent number				cex
	number of blocks in each extent		
	number of blocks in current extent		ces
	current block number in current extent.		cblk

compare the current block with the current extent size to see how much space has not yet been touched.

-- 
Jonathan Lewis