Re: SQL to list actual space used

In article <CG809E.L64_at_uk.ac.brookes>, p0070621_at_oxford-brookes.ac.uk (Tommy Wareing) writes:
|>Does anyone have a script to give the size of an INDEX on a table?
|>Or an algorithm to determine what the size would be?
|>
|>--
|> _________________________ _________________________
|> / Tommy Wareing \ / \
|>| p0070621_at_brookes.ac.uk X 'Bugger!' said Piglet |
|> \ 0865-483389 / \ /
|> ~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~
|>
Tommy, to determine the size of an index I've write this SQL script : set pause off
set ver off
col titre hea 'Nbre de bytes';
  select round((((&nbre_lignes * (11 + &nbre_col_ind + &avg_length_col_ind)) /

       ((2048 - 90) * (1 - &pctfree / 100))) * 2048) * 1.1) titre   from dual;
This formula is in the Database Administrator's Guide (version 6.0), chapter 16 Space Management, page 16-18 Managing Index Storage. Parameter blocksize (here 2048) can be different for you. Bye !

-- 
------------------------------------------------------------------------------
   _/_/_/_/    _/_/_/_/   _/_/_/_/   _/_/_/_/                  I use
  _/_/          _/_/       _/_/       _/_/               ORACLE v6.0.36.5.2
 _/_/          _/_/       _/_/       _/_/                       on        
_/_/_/_/ .  _/_/_/_/ .   _/_/ .   _/_/_/_/ .            DEC RISC ULTRIX v4.3
------------------------------------------------------------------------------
Yves NOEL   -   Database Administrator
C.I.T.I. (batiment M4)                             Phone : (33)  20.43.42.70
Universite des Sciences & Technologies de Lille    Fax   : (33)  20.43.66.25 
59655 Villeneuve d'Ascq Cedex - FRANCE             Email : noel_at_univ-lille1.fr
------------------------------------------------------------------------------