RE: Required to know the Empty Blocks of a Index

Hi,

After an idea from someone else on the list I wrote the following short script.

Feedback on whether the script is correct is welcome.

Regards,
Bruce Reardon
mailto:bruce.reardon_at_comalco.riotinto.com.au

set serveroutput on;

DECLARE     
   total_blocks		NUMBER ;
   total_bytes		NUMBER ;
   unused_blocks	NUMBER ;
   unused_bytes		NUMBER ;
   lastextf		NUMBER ;
   last_extb		NUMBER ;
   lastusedblock	NUMBER ;
   

   idx_name               VARCHAR2(30) ;

BEGIN idx_name := 'CHSE_ELMN_FK_I' ;

DBMS_OUTPUT.ENABLE(10000000); DBMS_SPACE.UNUSED_SPACE('schema_name', idx_name , 'INDEX', total_blocks,

   total_bytes,unused_blocks, unused_bytes, lastextf,    last_extb, lastusedblock) ;

DBMS_OUTPUT.PUT_LINE( 'Index ' || idx_name || ', total blks = ' || total_blocks ||

', total bytes = ' || total_bytes ) ;

DBMS_OUTPUT.PUT_LINE( ' unused blks = ' || unused_blocks ||

', unused bytes = ' || unused_bytes ) ;

END;
/

-----Original Message-----
From: Dash, Saroj (CAP,CEF) [mailto:Saroj.Dash_at_gecapital.com] Sent: Thursday, 21 December 2000 3:13

In a query I found the total no of blocks used ,total size ,minextents,maxextents ,empty blocks of a table.

But In index i am unable to find free / Empty blocks .

but i find leaf blovks ,avg no. of leaf blocks .

Please tell me how can I found free or empty blocks of a Index. Received on Wed Dec 20 2000 - 15:33:05 CST