rem  Procedure to calculate index space saving if they were rebuilt
rem=========================================================================
rem calculated Index size is based on the formula from Server Admin Guide
rem  resource consuming script, use accordingly
rem  Parameters : 1. Schema - Wild card can be used
rem               2. Table Name - Wild card can be used
rem               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
spool /tmp/ind_rspace.lst
declare
     wuser  varchar2 (15) := '&1';
     wtable varchar2 (30) := '&2';
     /* Indexes */
     cursor cind is
     select owner, table_owner, index_name, table_name, tablespace_name,
            ini_trans, pct_free
     from   dba_indexes where
            table_name like upper(wtable) and
            table_owner like upper(wuser);

     /* Indexe columns */
     cursor cicol (pindowner in varchar2, pindname in varchar2) is
     select column_name, column_length
     from   dba_ind_columns where
            index_owner = pindowner and
            index_name  = pindname;

     wcount       number := 0;
     wdate        varchar2 (25) := to_char(sysdate,'Mon DD, YYYY  HH:MI AM');

     wbs          number;
     wf           number;
     wd           number;
     wv           number;
     wbhs         number;
     wbpe         number;
     wadspb       number;
     wactindsize  number;
     wcalcindsize number;
     wpred        number;
     wblocks      number;
     wtotcalc     number := 0;
     wtotact      number := 0;
     wtotred      number := 0;

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

     wtotal_blocks               number;
     wtotal_bytes                number;
     wunused_blocks              number;
     wunused_bytes               number;
     wlast_used_extent_file_id   number;
     wlast_used_extent_block_id  number;
     wlast_used_block            number;


  begin
   dbms_output.enable(100000);

   dbms_output.put_line('********** INDEX REBUILD SIZE INFORMATION  ********** ' || wdate);
   dbms_output.put_line('*-----------------*---------------------------------*-------*---------*--------');
   dbms_output.put_line('TABLESPACE_NAME   TABLE.INDEX_NAME                  CURRENT ESTIMATED   PERCENT');
   dbms_output.put_line('.                                                   SIZE_KB   SIZE_KB REDUCTION');
   dbms_output.put_line('*-----------------*---------------------------------*-------*---------*--------');

   for rind in cind loop

     -- Actual space used by the index
     --
     dbms_space.unused_space (rind.owner, rind.index_name, 'INDEX',
                             wtotal_blocks,  wtotal_bytes,  wunused_blocks,
                             wunused_bytes, wlast_used_extent_file_id,
                             wlast_used_extent_block_id, wlast_used_block);

     -- 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 * rind.ini_trans);

     -- 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) * (rind.pct_free/100));

     -- Calculate column lengths
     --
     wcols := '';
     wf := 0;
     wv := 0;
     wd := 0;
     for ricol in cicol (rind.owner, rind.index_name) loop
        wcols := wcols || 'avg(vsize(' || ricol.column_name || ')) + ';
	-- 
	-- required for next step
	if ricol.column_length < 127 then
	   wf := wf + 1;
        else
	   wv := wv + 2;
        end if;
     end loop;
     wcols := wcols || '0';
     wsql := 'select ' || wcols || ' from ' || rind.table_owner || '.' || rind.table_name;
     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 ' || rind.table_owner || '.' || rind.table_name,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;
     --
     wactindsize := (wtotal_blocks - wunused_blocks) * wbs;
     --
     wpred := (wactindsize - wcalcindsize) / wactindsize * 100;

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

     wtotcalc := wtotcalc + wcalcindsize;
     wtotact  := wtotact  + wactindsize;

     -- Display output
     --
     dbms_output.put_line(rpad(rind.tablespace_name,18) || rpad(rind.table_name ||'.'||rind.index_name, 33) || to_char(wactindsize,'999,999') || to_char(wcalcindsize,'999,999.9') || to_char(wpred,'999999.9'));

     --
     wcount := wcount + 1;
   end loop;

   wtotred := (wtotact - wtotcalc) / wtotact * 100;
   dbms_output.put_line('*-----------------*---------------------------------*-------*---------*--------');
   dbms_output.put_line(rpad('TOTAL',51) || to_char(wtotact,'999,999') || to_char(wtotcalc,'999,999.9') || to_char(wtotred,'999999.9'));

   dbms_output.put_line('********** END INFO **********');
   if wcount =0 then
    dbms_output.put_line('******************************************************
');
    dbms_output.put_line('*                                                    *
');
    dbms_output.put_line('* Plese Verify Input Parameters... No Matches Found! *
');
    dbms_output.put_line('*                                                    *
');
    dbms_output.put_line('******************************************************
');
   end if;
  end;
/
set serveroutput off feedback on verify on pages 999
spool off
prompt
prompt Output saved at /tmp/ind_rspace.lst