set echo on;
rem ********************************************************************
rem * Author            : Rick Kupcunas
rem * Original          : 18-MAY-95
rem * Description       : Shows the size occupied by an index
rem * Usage             : start size_ndx.sql <date>  e.g. 03-MAR-95
rem *                                        <Program Name>
rem *                                        <Max run time (min.)>
rem *
rem ********************************************************************

/* @C:\rkupcuna05_ora\Oracle\SQL\size_ndx.sql */


spool size_ndx.rslt;

set pagesize 60;
set linesize 132;
break on "OWNER" on "TABLE";

prompt 'For this to be effective; run a full compute on the index'

select 
     substr(B.owner,1,10)                                   "OWNER",
     substr(B.table_name,1,30)                              "TABLE",
     substr(B.index_name,1,30)                              "INDEX",
     substr(to_char(A.num_rows, '99,999,999'),1,11)         "# ROWS"              
from  sys.dba_tables   A,
      sys.dba_indexes  B,
      v$parameter      C,
      sys.dba_segments D
where A.table_name    = upper('&&TABLE_NAME') and
      A.num_rows is not null and 
      A.blocks > 0                          and
      B.table_name    = A.table_name        and
      B.table_owner   = a.owner             and
      C.name          = 'db_block_size'     and
      D.segment_name  = B.index_name        and
      D.segment_type  = 'INDEX'             and
      D.owner         = B.owner
order by 1 asc,
      2 asc, 3 asc;

set echo on;

/* *********************************** */
/*                                     */
/*   A ...... size of index (in bytes) */
/*   B ...... size of index (in mBytes)*/
/*   C ...... average record size      */
/*                                     */
/* *********************************** */

set echo off;

select 
     substr(B.index_name,1,30)                                  "INDEX",
     substr(to_char(D.extents,'9,999'),1,6)                     "EXTs",
     substr(to_char(D.bytes, '9,999,999,999'),1,14)             "---A---",
     substr(to_char(D.bytes/(1024*1024), '999,999.99'),1,11)    "---B---",
     substr(to_char((B.LEAF_BLOCKS*C.Value)/A.num_rows, '999.99'),1,7)          "---C---"
from  sys.dba_tables   A,
      sys.dba_indexes  B,
      v$parameter      C,
      sys.dba_segments D
where A.table_name    = upper('&&TABLE_NAME') and
      A.num_rows is not null and 
      A.blocks > 0                          and
      B.table_name    = A.table_name        and
      B.table_owner   = a.owner             and
      C.name          = 'db_block_size'     and
      D.segment_name  = B.index_name        and
      D.segment_type  = 'INDEX'             and
      D.owner         = B.owner
order by 1 asc,
      2 asc, 3 asc;

set echo on;

/* *********************************** */
/*                                     */
/*   A ...... Space Used (in bytes)    */
/*   A1 ..... Space Used (in Mbytes)   */
/*   B ...... Space free (in bytes)    */
/*   B1 ..... Space free (in Mbytes)   */
/*                                     */
/* *********************************** */

set echo off;

select 
     substr(B.index_name,1,30)                              "INDEX",
     substr(to_char(B.LEAF_BLOCKS*C.Value, '9,999,999,999'),1,14) 
                                                            "---A---",
     substr(to_char((B.LEAF_BLOCKS*C.Value)/(1024*1024), '999,999.99'),1,11)          
                                                            "--A1--",
     substr(to_char((D.bytes - (B.LEAF_BLOCKS*C.Value)), '9,999,999,999'),1,14)
                                                            "---B---",
     substr(to_char((D.bytes - (B.LEAF_BLOCKS*C.Value))/(1024*1024), '999,999.99'),1,11)
                                                            "--B1--"
from  sys.dba_tables   A,
      sys.dba_indexes  B,
      v$parameter      C,
      sys.dba_segments D
where A.table_name    = upper('&&TABLE_NAME') and
      A.num_rows is not null and 
      A.blocks > 0                          and
      B.table_name    = A.table_name        and
      B.table_owner   = a.owner             and
      C.name          = 'db_block_size'     and
      D.segment_name  = B.index_name        and
      D.segment_type  = 'INDEX'             and
      D.owner         = B.owner
order by 1 asc,
      2 asc, 3 asc;

set echo off;
spool off;
commit;
undefine TABLE_NAME;
