rem  Procedure to calculate index space required
rem  calculated Index size is based on the formula from Server Admin Guide
rem  resource consuming script, use accordingly
rem
rem  Biju Thomas
rem  03/22/1999
rem
rem  Parameters : NONE - Hard coded values for...
rem               1. Schema - Change as required
rem               2. Table Name - Change as required
rem               3. Column Names  - Change as required
rem
rem
set serveroutput on feedback off verify off pages 0 echo off
prompt For large table this might take a while, please be patient...
prompt
declare
     
     winitrans       number := 2;
     wpctfree        number := 10;
     wuser           varchar2 (30) := 'BIJU';
     wtable          varchar2 (30) := 'SALARY';

     /* Table Columns */
     cursor ccol is
     select owner, table_name, column_name, data_length
     from   dba_tab_columns where
            table_name = upper(wtable) and
            owner      = upper(wuser) and
            column_name in ('EMPNUM', 'EMPDEPT','HIREDT');

     wbs          number;
     wf           number;
     wd           number;
     wv           number;
     wbhs         number;
     wbpe         number;
     wadspb       number;
     wcalcindsize  number;
     wblocks      number;
     wtotact      number := 0;
     wcolnames    varchar2 (200);

     wrows          number;
     wcolsize       number;
     wcols          varchar2 (500);
     wsql           varchar2 (500);
     wnumrows       number;
     wcursor_handle integer;
     wdummy         integer;

  begin
   dbms_output.enable(100000);

   -- Calculate required space for the index
   -- Using the method described in Server7 Adminstrators Guide

   -- Total Block Header Size = fixed header + variable transaction header
   --
   wbhs := 113 + (24 * winitrans);

   -- Available data space per data block = (block size - block header) -
   --                   ((block size - block header) * (pctfree/100))
   --
   select value into wbs
   from v$parameter 
   where upper(name) = 'DB_BLOCK_SIZE';
   --
   wadspb := (wbs - wbhs) - ((wbs - wbhs) * (wpctfree/100));

   -- Calculate column lengths
   --
   wcols := '';
   wf := 0;
   wv := 0;
   wd := 0;
   wcolnames := '* ';
   for rcol in ccol loop
     wcols := wcols || 'avg(vsize(' || rcol.column_name || ')) + ';
     -- 
     -- required for next step
     wcolnames := wcolnames || rcol.column_name || ' * ';
     if rcol.data_length < 127 then
        wf := wf + 1;
     else
        wv := wv + 2;
     end if;
   end loop;
   wcols := wcols || '0';
   wsql := 'select ' || wcols || ' from ' || wuser || '.' || wtable;
   wcursor_handle := dbms_sql.open_cursor;
   dbms_sql.parse(wcursor_handle, wsql, DBMS_SQL.V7);
   dbms_sql.define_column(wcursor_handle,1,wcolsize);
   wdummy := dbms_sql.execute_and_fetch(wcursor_handle, true);
   dbms_sql.column_value(wcursor_handle, 1, wcolsize);
   dbms_sql.close_cursor(wcursor_handle);
   wd := wcolsize;

   -- Calculate total average index value size
   -- bytes/entry = entry header + rowid length + F + V + D
   --
   wbpe := 2 + 6 + wf + wv + wd;

   -- Find total number of rows
   --
   wcursor_handle := dbms_sql.open_cursor;
   dbms_sql.parse(wcursor_handle,'select count(*) from ' || wuser || '.' || wtable, DBMS_SQL.V7);
   dbms_sql.define_column(wcursor_handle,1,wrows);
   wdummy := dbms_sql.execute_and_fetch(wcursor_handle, true);
   dbms_sql.column_value(wcursor_handle, 1, wrows);
   dbms_sql.close_cursor(wcursor_handle);

   -- Calculate number of blocks and bytes
   -- blocks = 1.05*(total rows/(floor(avg data per block/avg entry size)
   --
   wblocks := round(1.05 * (wrows / (floor(wadspb/wbpe))));
   wcalcindsize := wblocks * wbs;

   -- Convert to KB
   wcalcindsize := wcalcindsize / 1024;

   -- Display output
   --
   dbms_output.put_line('TOTAL INDEX SPACE REQUIRED FOR '|| wtable || ' ON INDEX WITH COLUMNS');
   dbms_output.put_line(wcolnames || ' IS KB: ' || to_char(wcalcindsize,'999,999'));

   --
end;
/
set serveroutput off feedback on verify on pages 999
prompt