rem============================================================================
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  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               4.OPDE
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) := 'OPDE';
     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