#!/usr/bin/ksh
# program smenu_desc_idx.sh
# Author : Bernard Polarski : 07/12/99
#                             rewritten 23 september 2005
#                             Bart Debersaques : Add size on index
#                             bpa              : Add count on unusable indexes
#                             bpa              : add -inv options for quick list all invalids
#                             bpa              : reviewed 15-december-2009
#                                              : Added move tablespace and recognition of function based and domain index
# set -x
    #--------------- comments section ---------------------
    # This script is part of the module 3 (DB utilities) in smenu
    # Purpose : List all about indexes 
    #--------------- Environement section ---------------------
    HOST=`hostname`
    #--------------- Function variables section ---------------------
    function help
    {
    cat <<EOF

       Usage  : 
                idx -u [owner]  -tbs                                          : List all indexes for given schema, -tbs show tablspaces
                idx -u [owner]  -t <table_name>                               : List all index for given table
                idx -u [owner]  -i [index] -t [table] -h -cf -fb -p -s
                idx -u [owner]  -i [index] -ntbs <new tablespace>  [-x]       : Gen. statements move index to new tablespace

                 -u <owner> : Schema owner of the index
            -i <index_name> : index name to process
               -ntbs <ntbs> : Move index to new tablespace

                       -cf  : Show clustering factor in percentage. The lower is the best, 100% is the worst
                       -fb  : Add the text of the function based index
                       -hg  : Show system overal histograms distribution among indexes
                      -inv  : List invalid indexes, partitions and subpartitions, order by owner
                       -ix  : Alternate info on index from ixora
                        -p  : List index (sub)partitions info; Add '-s' -> Segment size from dba_segments instead of analysed date
                     -sinv  : Generate Rebuild statement for ll invalid indexes, partitions and subpartitions
                      -tbs  : Show tablespace instead of columns_names
                        -x  : Execute command


Notes:   Clustering factor : - if near the number of blocks, then the table is ordered : index entries 
                               in a single leaf block tend to point to rows in same data block
                             - if near the number of rows, the table is randomly ordered : index entries in a single
                               leaf block are unlikely to point to rows in same data block

         Global stats      :   For partitioned indexes, YES means statistics are collected for the INDEX as a whole
                               NO means statistics are estimated from statistics on underlying index partitions or subpart.
         Pct_direct_access :   For secondary indexes on IOTs, rows with VALID guess

EOF
   }
if [[ -z "$1" ]];then
     help ; exit
fi
VAR_FIELD="to_char(a.LAST_ANALYZED,'YYYY-DD-MM HH24:MI') la,"
VAR_FIELD2=" clustering_factor cf, "
TIT_IDX_NAME_LEN=30
TIT_COL_NAME=22
TIT_TABLE_NAME=28
typeset -u fowner
typeset -u findex
typeset -u ftable
choice=default
while [[ -n "$1" ]]
    do
      case $1 in
        -cf ) CLUSTER_FACTOR=TRUE ;;
       -fb ) FB=TRUE ;;
         -h ) help ; exit ;;
         -i ) findex=$2 ; shift;;
       -inv ) choice=LIST_INVALID;;
        -ix ) choice=IX ;;
        -hg ) choice=HISTO;;
      -ntbs ) choice=MOVE_TBS ; typeset -u  ftbs=$2 ; shift ;;
         -p ) choice=PART ;;
         -t ) ftable=$2 ; AND_TABLE=" and b.table_name ='$ftable' " ; shift;;
         -s ) VAR_FIELD="(select sum(bytes/1024/1024) bytes from dba_segments
                  where segment_name = b.index_name and owner = b.index_owner group by owner,segment_name) tot_seg_mb,"
              ;;
     -sinv ) choice=REBUILD_INVALID;;
      -tbs ) COL_OR_TBS=b.tablespace_name ;;
        -u ) fowner=$2 ; shift ;;
        -x ) EXECUTE=YES ;;
        -v ) VERBOSE=TRUE;;
         * ) help
             exit ;
      esac
      shift
    done

    #--------------- Process section             ---------------------
    #--------------- Get system password section ---------------------
    . $SBIN/scripts/passwd.env
    . ${GET_PASSWD}
    if [  "x-$CONNECT_STRING" = "x-" ];then
          echo "could no get a the password of $S_USER"
          exit 0
    fi
    #--------------- Process section ---------------------

# a table name is given but no index and no owner: 
# we output all index for this table or the list of table owner if multiple occurence exists
if [ -n "$ftable" -a -z "$findex" -a -z "$fowner" ];then
   ret=`sqlplus -s "$CONNECT_STRING" <<EOF
        set feed off pagesize 0 head off
          select  trim(to_char(count(*))) cpt from dba_tables where table_name=upper('$ftable');
EOF`
   if [ -z "$ret" ];then
       echo
       echo "Currently, there is no entry in dba_tables for $ftable"
       exit
    elif [ "$ret" -eq "0" ];then
       echo
       echo "Currently, there is no entry in dba_tables for $ftable"
       exit
    elif [ "$ret" -eq "1" ];then
        fowner=`sqlplus -s "$CONNECT_STRING" <<EOF
        set feed off pagesize 0 head off
        select owner from dba_tables where table_name=upper('$ftable');
EOF`
    elif [ "$ret" -gt "0"  ];then
      if [ -z "$fowner" ];then
         echo
         echo " there are many owner for table for $ftable:"
         echo " Use :  idx -u $ftable -u <owner> to view all indexes for this table"
         echo
         sqlplus -s "$CONNECT_STRING" <<EOF
         set feed off pagesize 66 head on
         select owner, table_name , 'table' from dba_tables where table_name=upper('$ftable') ;
EOF
         exit
      fi
    fi
fi

# Index is given, retrieve owners or propose list:

if [ -n "$findex" -a -z "$fowner" ];then
   unset ret
   ret=`sqlplus -s "$CONNECT_STRING" <<EOF
set feed off pagesize 0 head off verify off trimspool on
col cpt for A11 
select trim(to_char(count(*))) cpt from dba_indexes where index_name='$findex' ;
EOF`
   if [ -z "$ret" ];then
      echo 
      echo "Currently, there is no entry in dba_index for $findex"
      exit
   elif [ "$ret" -eq 0 ];then
      echo 
      echo "Currently, there is no entry in dba_index for $findex"
      exit
   elif [ "$ret" -eq 1 ];then
      fowner=`sqlplus -s "$CONNECT_STRING" <<EOF
set feed off pagesize 0 head off
select owner from dba_indexes where index_name='$findex' ;
EOF`
     FOWNER=" owner = '$fowner' "
     AND_FOWNER=" and  $FOWNER"
   elif [ "$ret" -gt 0  ];then
      if [ -z "$fowner" ];then
        echo 
        echo " there are many owners for the index '$findex':"
        echo
        echo " Use : "
        echo " idx -i $findex -u <owner> "
        echo
       sqlplus -s "$CONNECT_STRING" <<EOF
set feed off pagesize 66 head on lines 190 
select owner, index_name, index_type  from dba_indexes where index_name='$findex' ;
EOF
    exit
     fi
   fi
fi

#...............................................
# Move index to a new tablespace
#................................................
if [  "$choice" = "MOVE_TBS" ];then
  [[ -z "$ftbs" ]] && echo "I need a tablesspace name to move to a new tablespace" && exit
  [[ -z "$fowner" ]] && echo "I need at least schema name or a index name or maybe even both" && exit
  [[ -z "$findex" ]] && echo "I need at least schema name or a index name or maybe even both" && exit

if [ -n "$fowner" ];then
      AND_IOWNER="  and ai.owner = '$fowner' "
      AND_PART_OWNER="  and aii.index_owner = '$fowner' "
fi
if [ -n "$findex" ];then
      AND_INDEX="  and ai.index_name = '$findex' "
fi

# ... I am not in mood to be clear, so ....
[[  -z "$EXECUTE" ]]
doit=$?

sqlplus -s "$CONNECT_STRING" <<EOF
set pages 66 lines 190 serveroutput on size unlimited
declare
  P_ITBS         varchar2(30) :='$ftbs' ;
  P_OWNER        varchar2(30) :='$fowner' ;
  P_INDEX        varchar2(30) :='$findex' ;
  v_sql          varchar2(512) ;
  itbs_exists    varchar2(5) ;
  v_run_now      number :=$doit ;

 -- tbl_or_idx  : 1=table/IOT    2=index
 procedure doit ( action in number , sqlcmd in varchar2 ) is
    begin
       dbms_output.put_line(' itbs_exists=' || itbs_exists   );
        if action = 1 and itbs_exists = 'TRUE' then
           dbms_output.put_line('Doing : '|| sqlcmd || ';') ;
           execute immediate sqlcmd ;
       else
           dbms_output.put_line(sqlcmd || ';') ;
       end if;
   end;

begin
   -- check index to move is not an iot
   if v_run_now = 0 then
      dbms_output.put_line('Rem No execution requested');
   else
      dbms_output.put_line('Rem Execution of command requested');
   end if;

   -- check if ftbs exists
   if P_ITBS is not null then
      select decode(count(*),0,'FALSE','TRUE') into itbs_exists from dba_tablespaces where tablespace_name = P_ITBS  ;
      dbms_output.put_line('Rem Tablespace for index ' || P_ITBS || ' exists : ' ||itbs_exists );
      dbms_output.put_line('Rem');
   end if;
      for idx in (select index_name, ai.owner,  ai.partitioned from all_indexes ai
                         where 1=1  $AND_IOWNER $AND_INDEX )
      loop
         dbms_output.put_line('Rem');
         dbms_output.put_line('Rem Index name : ' ||idx.index_name );
         dbms_output.put_line('Rem');
         if  idx.partitioned = 'NO' then
             v_sql := 'ALTER INDEX '||idx.owner||'.'||idx.index_name||' REBUILD TABLESPACE ' || P_ITBS ;
              doit(v_run_now,v_sql);
         else
             for fpart in (select partition_name from all_ind_partitions where index_owner = idx.owner and index_name = idx.index_name)
             loop
                 v_sql:='ALTER INDEX ' || idx.owner|| '.'|| idx.index_name || ' REBUILD PARTITION ' || fpart.partition_name
                         ||' TABLESPACE ' || P_ITBS ;
                 doit(v_run_now,v_sql) ;
             end loop ;

             for fpart in (select subpartition_name from all_ind_subpartitions where index_owner = idx.owner and index_name = idx.index_name)
             loop
                 v_sql:='ALTER INDEX '||idx.owner||'.'||idx.index_name||' REBUILD SUBPARTITION ' ||fpart.subpartition_name ||
                       ' TABLESPACE ' || P_ITBS ;
                 doit(v_run_now,v_sql);
             end loop ;
         end if;
      end loop;
end;
/
EOF

exit

#................................................
# list invalid indexes
#................................................
elif [  "$choice" = "HISTO" ];then

SQL="
set termout off verify off feed off lines 132 pages 66
select  sum(case when max_cnt > 2 then 1 else 0 end) histograms,
                sum(case when max_cnt <= 2 then 1 else 0 end) no_histograms
    from (
        select table_name, max(cnt) max_cnt
                from (
                        select table_name, column_name, count(*) cnt
                                from dba_tab_histograms
                                group by table_name, column_name
                ) group by table_name
   )
/
"
#................................................
# list invalid indexes
#................................................
elif [ "$choice" = "IX"  ];then
#-------------------------------------------------------------------------------
#--
#-- Script:     index_access_paths.sql
#-- Purpose:    *** called from table_access_paths.sql ***
#--
#-- Copyright:  (c) Ixora Pty Ltd
#-- Author:     Steve Adams (http://www.ixora.com.au/scripts/sql/index_access_paths.sql)
#-- Adapted to Smenu by B. Polarski
#--------------------------------------------------------------------------------



if [ -z "$findex" ];then
   echo "I need an index name"
   exit
fi

sqlplus -s  "$CONNECT_STRING" <<EOF

set termout off verify off feed off
col value new_value BlkSz noprint
col object_id new_value idx_id noprint
col table_name new_value table_name noprint
col iotbit new_value IotBit noprint
col rpb new_value RowsPerBlock noprint

select value from v\$parameter where name = 'db_block_size'
/
select object_id from dba_objects  where object_type='INDEX' and object_name = '$findex' and owner = '$fowner'
/
select table_name from dba_indexes  where index_name = '$findex' and owner = '$fowner'
/
select decode(bitand(t.property, 64), 64, 'YES', 'NO')  iotbit from tab\$ t, obj\$ o
       where o.obj# =  t.obj# and o.type# = 2 and o.name = '&table_name' 
         and o.owner# = (select user# from user\$ where name  = '$fowner') ;
/
select (1-(avg_space/&BlkSz))* round(nvl(floor(&BlkSz - 66 - INI_TRANS * 24)/greatest(AVG_ROW_LEN + 2, 11), 1),0)  rpb
  from dba_tables where table_name = '&table_name' and owner = '$fowner'
/
set termout on
set heading on pause off  feed off  verify off pagesize 56 linesize 132

column blocks         format 999999999
column density        format 9999999
column key_values     format 99999999999
column entries        format 99999999999 justify right
column table_ordering format 990.9999 head "ordering"

select /*+ ordered */
  o.name  index_name, i.leafcnt blocks,
  (
    least( 1, (
        select
          (
            sum(
              decode(sign(ic.pos# - nvl(i.spare2, 0)), 1, h.avgcln + 0.5, 0)
            ) + 11
          ) * i.rowcnt +
          (
            sum(
              decode(sign(nvl(i.spare2, 0) - ic.pos#), 1, h.avgcln + 0.5, 0)
            ) + 11
          ) * i.leafcnt
        from
          sys.icol\$  ic,
          sys.hist_head\$  h
        where
          ic.obj# = i.obj# and
          h.obj# (+) = ic.bo# and
          h.intcol# (+) = ic.intcol#
      ) / (i.leafcnt * (&BlkSz - 108 - i.initrans * 24))
    )
  )  density,
  i.distkey  key_values,
  i.rowcnt  entries,
  decode(
    '&IotBit',
    'YES',
    '           n/a',
    (
      round(1 - (i.clufac - i.rowcnt / &RowsPerBlock + 1) / i.rowcnt / (1 - 1 / &RowsPerBlock),4)
    )
  )  table_ordering
from
  sys.obj\$  o,
  sys.ind\$  i
where
  o.obj# = &idx_id and
  i.obj# = o.obj#
/

column storage         format a10
column bytes           format 99999 justify right
column buckets         format 999999
column popular         format a7
column non_pop_density format a15

select
  tc.name  column_name,
  decode(sign(ic.pos# - nvl(i.spare2, 0)), 1, 'NORMAL', 'COMPRESSED')  storage,
  h.avgcln  bytes,
  h.bucket_cnt  buckets,
  decode(
    h.bucket_cnt,
    1,
    null,
    decode(
      1 + h.bucket_cnt - h.row_cnt,
      0,
      '    nil',
      ((1 + h.bucket_cnt - h.row_cnt) / h.bucket_cnt)
    )
  )  popular,
  decode(
    sign(h.density * i.rowcnt - least(100, greatest(i.rowcnt/100, 10))),
    -1,
    lpad(to_char(h.density * i.rowcnt, '999') || decode(round(h.density * i.rowcnt), 1, '  row', ' rows'), 15)
  )  non_pop_density
from
  sys.ind\$  i,
  sys.icol\$  ic,
  sys.col\$  tc,
  sys.hist_head\$  h
where
  i.obj# = &idx_id and
  ic.obj# = i.obj# and
  tc.obj# = ic.bo# and
  tc.intcol# = ic.intcol# and
  h.obj# (+) = tc.obj# and
  h.intcol# (+) = tc.intcol#
order by
  ic.pos#
/


EOF
#................................................
elif [  "$choice" = "REBUILD_INVALID"  ];then

    if [ -n "$fowner" ];then
        AND_OWNER=" and owner = '$fowner' "
        AND_IDX_OWNER=" and index_owner = '$fowner' "
   fi
   TITLE="Generate statments to rebuild invalid indexes"

   SQL=" set heading on pause off pagesize 56 linesize 132
select 'alter index ' || owner||'.'|| index_name || ' rebuild online ;'
             from dba_indexes where status != 'VALID' and status != 'N/A' $AND_OWNER
union
select 'alter index ' ||  index_owner ||'.'|| index_name || ' rebuild partition ' || partition_name || ' ;'
              from dba_ind_partitions where status != 'USABLE' and status != 'N/A' $AND_IDX_OWNER
union
select  'alter index ' || index_owner ||'.'|| index_name || ' rebuild  subpartition ' || subpartition_name || ' ;'
            from dba_ind_subpartitions where status != 'USABLE' and status != 'N/A' $AND_IDX_OWNER
/
"
#................................................
elif [  "$choice" = "LIST_INVALID"  ];then
SQL="
set heading on pause off pagesize 56 linesize 132
select owner, index_name, ' ' partition_name,status from dba_indexes where status != 'VALID' and status != 'N/A'
union
select  index_owner owner, index_name, partition_name, status from dba_ind_partitions where status != 'USABLE' and status != 'N/A'
union
select  index_owner owner, index_name, subpartition_name, status from dba_ind_subpartitions where status != 'USABLE' and status != 'N/A'
/
"
#................................................
# for all index if not index name given
#................................................
elif [ -z "$findex" ];then

    if [ -z "$COL_OR_TBS" ] ;then
        COL_OR_TBS="column_name"
    else
        COL_OR_TBS=" case nvl(b.tablespace_name,'0')
            when '0' then
                     case b.partitioned
                          when 'NO' then
                               case b.temporary
                                    when 'N' then '--Func base or Domain --'
                                    else '-- Temporary --'
                               end
                          else '-- partitioned --'
                     end
            else b.tablespace_name
       end tablespace_name "

    fi
    TITLE="List all indexes for schema '$fowner'"
SQL="
set heading on pause off pagesize 56 linesize 190
column dg format A3 heading 'Par'
column f1 format a$TIT_IDX_NAME_LEN heading 'Index|Name'
column column_name format a$TIT_COL_NAME heading 'Column|Name'
column f0 format a$TIT_TABLE_NAME new_value the_table heading 'Table|Name'
column tablespace_name format a22 heading 'Tablespace'
column f2 format a1 heading 'U'
col cf format 999999999 head 'Clust| Factor'
col bytes format 99999999 head 'Size(m)'
col dk format 999999999 head 'Distinct| Keys' justify c
col la format A13  head 'last analysed'
col st format A3  head 'Sta|tus'
col un format  999999  head 'Unusable|Sub indx'
col status format a8
col meg format 9999999 head 'Size(m)'
col pct format 990.9 head 'Clust|f(%)' justify c
column blocks format 9999999 head 'Table|blocks' justify c
col idx_type for a10 head 'Index| Type' 
col leaf_blocks head 'Leaf| blocks' for 99999999 justify c
set head off feed off


set head on feed on verify off
break on f0 on fw skip 1 on f0 on ft on f1 on f2

select b.table_name f0, b.index_name f1, substr(uniqueness,1,1)   f2,
       $COL_OR_TBS  , clustering_factor cf,
       decode(b.num_rows,0,0,((CLUSTERING_FACTOR/c.blocks)/(b.NUM_ROWS/c.blocks))*100) pct,
       $BLOCKS DISTINCT_KEYS dk, b.NUM_ROWS, 
       leaf_blocks, 
       substr(b.status,1,3) st, ' '||substr(b.DEGREE,1,2) dg, 
       case b.index_type 
            when 'FUNCTION-BASED NORMAL' then 'Func based'
            when 'FUNCTION-BASED DOMAIN' then 'Func domain'
          else b.index_type
       end idx_type
from dba_ind_columns a, dba_indexes b, dba_tables c
where b.table_owner     = '$fowner'  $AND_TABLE  and
      a.index_name      = b.index_name           and
      a.index_owner (+) = b.table_owner          and
      a.table_name  (+) = b.table_name           and
      c.table_name      = b.table_name           and 
      c.owner           = b.table_owner
order
   by b.table_type, b.table_name, b.index_name, column_position 
/
"
# ...............................................................................
elif [[ "$choice" = "PART" ]];then

    TITLE="List partitions for '$findex'"
    SQL="
       set head on lines 190 pages 66 verify off feed off pause off
       break on partition_position on partition_name on status on report on tablespace_name
       COL sel                  FORMAT  999999999 heading 'Num rows'
       COL dstk                 FORMAT  999999999 heading 'Distinct rows'
       COL partition_position   FORMAT  9999 heading 'Part| Pos'
       COL fsize                FORMAT  9999999   heading 'Size (m)'
       COL partition_name       FORMAT  A25 head 'Partition name'
       COL subpartition_name    FORMAT  A25 head 'System generated|Subartition name'
       COL tablespace_name      FORMAT  A25   justify c HEAD ' Tablespace name'
       col last_analyzed        format  A18  head 'Last Analyzed'
       col status               format  A8
       col db_block_size new_value fsize noprint
       select value db_block_size from v\$parameter where name = 'db_block_size' ;
       comp sum of fsize on report

       select a.partition_position, a.partition_name,  b.subpartition_name,
             (nvl(b.leaf_blocks,a.leaf_blocks) * &fsize )/1024/1024 fsize, 
              rpad(nvl(b.tablespace_name,a.tablespace_name),25) tablespace_name, 
              nvl(b.num_rows,a.num_rows) sel , nvl(b.distinct_keys,a.distinct_keys) dstk, $VAR_FIELD 
              -- to_char(nvl(b.last_analyzed,a.last_analyzed),'DD-MM-YY HH24:MI') la 
             substr(nvl(b.status,a.status),1,6) status
      from 
            dba_ind_partitions a, 
            dba_ind_subpartitions b
          where  
                  a.index_name     = '$findex' and  a.index_owner= '$fowner'
             and  a.index_owner    = b.index_owner  
             and  a.index_name     = b.index_name  
             and  a.partition_name = b.partition_name  order by a.partition_name; "
# ...........................................
elif [[  "$choice" = "default" ]];then

    if [[ -n "$CLUSTER_FACTOR" ]];then
             ftable=`sqlplus -s "$CONNECT_STRING" <<EOF
set feed off pagesize 0 head off verify off trimspool on
select table_name from dba_indexes where index_name='$findex' and owner = '$fowner' ;
EOF`
             VAR_FIELD2=" round(decode(num_rows,0,0,((CLUSTERING_FACTOR/(select blocks from dba_tables 
                           where table_name = '$ftable' and owner='$fowner' ))
                           /(NUM_ROWS/( select blocks from dba_tables 
                           where table_name = '$ftable' and owner='$fowner')) ))*100,1) pct ,"
              BLOCKS="c.blocks,"
    fi
 
    #  add text for function based index
    if [[ -n "$FB" ]];then
        SQL_FB="select COLUMN_POSITION, COLUMN_EXPRESSION from
                     DBA_IND_EXPRESSIONS a where INDEX_NAME = '$findex' and index_owner = '$fowner';"
    fi
    TITLE="Table List - Index stats for $findex"
    SQL=" col db_block_size new_value fsize noprint
col tablespace_name for a26 head 'Tablespace'
set head off feed off
select value db_block_size from v\$parameter where name = 'db_block_size' ;
set heading on pause off  feed off  verify off pagesize 56 linesize 132

break on f1 on f2 on descend on tablespace_name on dg on PARTITIONED on GLOBAL_STATS on USER_STATS on PCT_FREE on FREELISTS on FREELIST_GROUPS on PCT_DIRECT_ACCESS on status
set lines 190
select a.index_name f1, substr(uniqueness,1,1)  f2,
       column_name  f3, COLUMN_POSITION cp, descend,
       case nvl(tablespace_name,'0')
            when '0' then
                     case partitioned
                          when 'NO' then
                               case temporary 
                                    when 'N' then '--Func base or Domain --'
                                    else '-- Temporary --'
                               end
                          else '-- partitioned --' 
                     end
            else tablespace_name
       end tablespace_name,
       substr(DEGREE,1,2) dg,
       PARTITIONED ,GLOBAL_STATS,  USER_STATS,
       PCT_FREE, FREELISTS, FREELIST_GROUPS,
       PCT_DIRECT_ACCESS,
       status
from dba_ind_columns a, dba_indexes b 
where  b.index_name      = '$findex'        and
       b.owner           = '$fowner'        and
       a.index_name      = b.index_name     and
       a.index_owner     = b.owner
order
   by b.index_name, column_position
/
break on compression
prompt
-- col table_name new_value table_name noprint
select  Blevel, index_type typ, LEAF_BLOCKS,
        AVG_DATA_BLOCKS_PER_KEY , AVG_LEAF_BLOCKS_PER_KEY , 
        $VAR_FIELD2 DISTINCT_KEYS dk, NUM_ROWS,
        (select sum(bytes/1024/1024) bytes from dba_segments 
                where segment_name = '$findex' and owner = '$fowner') bytes,
        decode(nvl(distinct_keys,0),0,0,num_rows/distinct_keys) sel,
        to_char(LAST_ANALYZED,'DD-MM-YY HH24:MI') la , compression
from 
     dba_indexes 
where 
     index_name = '$findex'  and owner = '$fowner'
/
$SQL_FB
prompt
prompt
"
fi

if [ -n "$VERBOSE" ];then
   echo "$SQL"
fi
       cat <<EOF
MACHINE $HOST - ORACLE_SID : $ORACLE_SID                                     Page:   1
Date              -  `date +%a' '%d' '%B' '%Y' '%H:%M:%S`
Username          -  $S_USER  $TITLE
EOF


sqlplus -s "$CONNECT_STRING" <<EOF
col AVG_DATA_BLOCKS_PER_KEY format 999999 head 'Avg data| Blocks | Per Key'
col AVG_LEAF_BLOCKS_PER_KEY format 999999 head 'Avg leaf| Blocks | Per Key'
col Blevel format 999 head 'Blevel' justify c
col bytes format 99999999 head 'Size(m)' justify c
col compression format A8 head 'Compress' justify c
col cf format 9999999999 head 'Clust| Factor'
col cp format 999 heading 'Col|Pos'
col FREELISTS format 99 head 'Free|List'
col FREELIST_GROUPS format 99 head 'Free| List|Group'
col descend format a4 heading 'Ord'
col dg format A4 heading 'Para|llel'
col dk format 9999999999 head 'Distinct| Keys'
col f1 format a32 heading 'Index Name'
col f2 format a1 heading 'U'
col f3 format a24 heading 'Column|Name'
col la format A16  head 'last analysed'
col leaf_blocks format 99999999 head 'Leaf|Blocks' justify c
col num_rows format 9999999999 head 'Num rows'
col pct format 990.9 head 'cf(%)'
col USER_STATS format A5 head 'user |Stat' justify c
col GLOBAL_STATS format A6 head 'Global |Stats' justify c
col partitioned format A4 head 'Part|tion'
col PCT_DIRECT_ACCESS format 999999 head '  Pct |Direct|Access' justify c
col pct_free format 9999 head 'Pct|Free' justify c
col sel format 99999999.9  head 'Selectivity' justify c
col status format A5 head 'Sta|tus'
col table_name heading 'Table,name'
col table_owner heading 'Table owner'
col typ format A10 head 'Index|type' justify c

$SQL
EOF