#!/usr/bin/ksh
#  set -xv
# author  : B. Polarski
# program : smenu_gather_stat_tbl.ksh
# date    : 01 October 2005
# Apapted to Smenu by B. Polarski
# Modified:           04 October
HOST=`hostname`
HOST=`echo $HOST | awk '{ printf ("%-+15.15s",$1)  }'`
# -----------------------------------------------------------------------------------------------------------------------
function help
{

  more <<EOF
     Gather statistics utility: This script output on the screen the gather statistics statement.  You can execute it if you add -x

             sta -u <OWNER>
             sta -u <OWNER> -t <TABLE>  [-p <Percent>] [-f] [-s <stattab>] [-o <stat owner>] [-n <statid>]
                    -part [partname] -col <col_name> ... -col <col_name> -c -cl -cp -x -ka <num buckets> -kc <string> -lock|-unlock
             sta -u <OWNER> -i <INDEX>  [-p <Percent>] [-s <stattab>] [-o <stat owner>] [-n <statid>] -part [partname] -cp -cl -x
             sta -u <OWNER> -l |-m  [-s <stat table>] [-b <TABLESPACE>]
             sta -fx | -dx/-dy     OR      sta -fy [start |stop] [-int <minutes>]
             sta -e -s <stattab> -f [-t <TABLE>] [-i <INDEX>] [-u <OWNER>] -c
             sta -e -u <OWNER> -y
             sta -a -s <stattab> [-t <TABLE>] [-i <INDEX>] [-u <OWNER>] -c -n <statid> -o <statowner>
             sta -del -s  <stattab> [-t <TABLE>] [-i <INDEX>] [-u <OWNER>] -c

    -fx : gather stat for fixed tables                                   -dx     : delete stat for fixed tables
    -fy : gather system statistics                                       -dy     : delete system statistics 
     -p : Percent sample on each objects (default to 5%)                 -part   : Partition name
     -i : the index                                                      -n      : statid
     -v : show execution                                                 -x      : execute the output of this scripts
     -y : export or import will also do system statistics                -lp     : List dbms_stat default parameters
     -t : the table                                                      -d      : degree (default to 2x cpu)
          -col <columns name> ..... -col <columns name> gather stats only for columns name list
     -f : Copy gathered statistics to sm_stattab. It is needed if you intend to export/import statistics 
     -a : import stats from <stattab> into schema -u <OWNWER> or into table -t <TABLE> or -i <INDEX> -n <statid>
     -c : Set cascade=TRUE to gather statistics also on the indexes for this table. Index statistics gathering is
          not parallelized. This option is equivalent to running the GATHER_INDEX_STATS procedure on each of the
          table's indexes.  (default is FALSE). This parameter is only for table
    -ka : For table only; set the number of histogram buckets for all columns: -k 100
    -kc : For table only; set the number of histogram buckets per each columns. Give the string list used by method_opt
   -del : delete stats from <stattab> and schema -u <OWNWER> -n <statid>
                -t <TABLE>  -part [partname] -cp -cl -ci
                      -cp :  delete cascade to partitions if partname is NULL
                      -cl :  delete cascade to columns
                      -ci :  delete cascade to indexes
                -i <INDEX> -part [partname]  -cp
     -g : granularity, values are :    DEFAULT         Gather global- and partition-level statistics
                                       PARTITION       Gather partition-level statistics.
                                       SUBPARTITION    Gather subpartition-level statistics.
                                       GLOBAL          Gather global statistics
                          (default) *  ALL             Gather all (subpartition, partition, and global) statistics
     -e : export stats to <stattab> from schema -u <OWNWER>, from table -t <TABLE> or from index -i <INDEX>
     -l : list existing stat table and statid. With -s <stattab>, gives details per type for each object
     -lock|-unlock : if table name is not given then lock/unlock stats at schema level
     -m : Create stat table given by -s <table stat> -b <TABLESPACE> create stattable in TABLESPACE otherwise default is used
     -o : <stat owner> can be different than the objects analyzed
     -s : <stattab> Table that will hold stat of the table. (default is sm_stattab)
     -u : the table(s) or index(es) owner. If only the owner is given, then gather stats for schema

     Note :           sta -u <user> -l -s <stattab>       give types per object.
                 Use  tbl [-u <user>] -t <table> -s       to see stats on column for a table

EOF

exit
}
# -----------------------------------------------------------------------------------------------------------------------
if [ -z "$1" ];then
   help; exit
fi
typeset -u ftable
typeset -u findex
typeset -u fowner
typeset -u fgran
typeset -u fcasc
typeset -u fstattatb
typeset -u FVAR
typeset -u COL_LIST
unset l_part
CHECK_STR="#DEFAULT#PARTITION#SUBPARTITION#GLOBAL#ALL#"
CREATE_STATTAB=FALSE
LIST_STAT=FALSE
EXECUTE=FALSE
EXPORT_STAT=FALSE
IMPORT_STAT=FALSE
STAT_SYSTEM=FALSE
fdate=`date +%m%d%H%M`
fperc=dbms_stats.auto_sample_size
fgran='ALL'
fcasc=FALSE
fcpu=`$SBIN/module2/s1/smenu_list_init_param.sh -p cpu_count`
fdegree=`expr $fcpu \* 2`
while [ -n "$1" ]
do

  case "$1" in
   -a ) IMPORT_STAT=TRUE;;
   -b ) FVAR=$2
        TBS=", '$FVAR'"
        shift ;;
   -c ) fcasc=TRUE  ;;
 -del ) fdel=TRUE ;;
  -cl ) DEL_CASC_COL=",cascade_columns => TRUE ";;
  -cp ) DEL_CASC_PART=",cascade_parts => TRUE ";;
 -col ) if [ -n "$COL_LIST" ];then
             COL_LIST="${COL_LIST}, $2"
        else
             COL_LIST="$2"
        fi
        shift ;;
   -d ) fdegree=$2
        shift ;;
   -e ) EXPORT_STAT=TRUE; COPY_ST_STTAB=TRUE;;
   -g ) fgran=$2
        if [ -n "${CHECK_STR##*$fgran*}" ];then
          echo "########################################"
          echo  "Wrong granularity value "
          echo "########################################"
          echo  "\n Must be in ; \n`echo $CHECK_STR |tr '#' '\n'`"
          help
        fi
        shift;;
   -h ) help;;
  -fx ) CHOICE=GFIX ; GET=TRUE;;
  -fy ) CHOICE=GSYS ; GET=TRUE 
        if [ "$2" = "-x" -o -z "$2" ] ;then
              :
         else
            MODE=$2; shift 
         fi;;
  -dy ) CHOICE=GSYS ; GET=FALSE ;;
  -dx ) CHOICE=GFIX ; GET=FALSE;;
   -i ) findex=$2 ; shift ;;
 -int ) INT_MINUTES=$2; shift ;;
   -l ) LIST_STAT=TRUE ;;
  -lp ) CHOICE=LIST_PARAM ;;
  -ka ) METHOD_OPT=" ,method_opt=>'for all columns size $2',";shift ;;
  -kc ) METHOD_OPT=" ,method_opt=>'$2',";shift ;;
   -n ) l_fstatid=$2
        shift ;;
   -o ) fstat_owner=$2
        shift ;;
   -m ) CREATE_STATTAB=TRUE  ;;
   -p ) fperc=$2
        shift ;;
-part ) l_part=$2
        shift ;;
   -s ) l_fstattab=$2
        shift ;;
   -t ) ftable=$2
        shift ;;
   -u ) fowner=$2
        shift ;;
   -v ) SETXV="set -x" ;;
   -x ) EXECUTE=TRUE ;;
   -y ) STAT_SYSTEM=TRUE ;;
   -f ) COPY_ST_STTAB=TRUE ;;
  -lock ) LOCK=TRUE ; unset UN ;;
-unlock ) LOCK=TRUE ; UN=UN ;;
   * )  echo "Invalid argument $1"
        $help ;;
 esac
 shift
done

# -------------------------------------------------------------------------------------
function ret_part
{
   arg=$1
   cpt=$2
   TYPE=$3        # value is TAB or IND
   if [ $cpt -gt 0 -a ! "$fgran" = "PARTITION" ];then
       case $arg in
         PART_NAME ) echo SUBPARTITION_NAME ;;
         DBA_TAB   ) echo DBA_${TYPE}_SUBPARTITIONS ;;
       esac
       if [ "$fgran" = "ALL" -o "$fgran" = "SUBPARTITION" ];then
          :
       else
          echo "Garther stats for subpartitions are only done for Granularity = \"ALL\" or \"SUBPARTITION\""
          echo "If you are not happy with that, you can send your instults at ceo@Oracle.com"
          echo "Aborting ==>"
          exit 0
      fi
   else
     case $arg in
        PART_NAME ) echo PARTITION_NAME ;;
        DBA_TAB   ) echo DBA_${TYPE}_PARTITIONS ;;
     esac
   fi
}
# -------------------------------------------------------------------------------------
function get_ind_nbr_part
{
      var=`sqlplus -s "$CONNECT_STRING"<<EOF
      set pagesize 0 feed off head off pause off
      select max(subpartition_count) from dba_ind_partitions where index_owner = '$fowner' and index_name = '$findex' ;
EOF`
      if [ -z "$var" ];then
         echo 0
      else
        echo "`echo $var | awk '{print $1}'`"
      fi
}
# -------------------------------------------------------------------------------------
function do_execute
{
sqlplus -s "$CONNECT_STRING" <<EOF

ttitle skip 2 'MACHINE $HOST - ORACLE_SID : $ORACLE_SID '  right 'Page:' format 999 sql.pno skip 2
column nline newline
set pagesize 66  linesize 100  termout on pause off  embedded on  verify off  heading off
select 'Date              -  '||to_char(sysdate,'Day Ddth Month YYYY  HH24:MI:SS'),
       'Username          -  '||USER  nline,
       'Execute DBMS_STATS ' nline
from sys.dual
/

set head off PAGESIZE 0
set linesize 124
Prompt Runing $FIL_EXECUTE
@$FIL_EXECUTE
/
EOF
echo '********************************************************************'
echo "log file: $FOUT"
echo '********************************************************************'
echo
}
# -------------------------------------------------------------------------------------
function get_tab_nbr_part
{
   # normal table return -1, partition, return 0, subpartition return count(subpartitions)
   var=`sqlplus -s "$CONNECT_STRING"<<EOF
     set pagesize 0 feed off head off pause off
     select nvl(max(subpartition_count),-1) from dba_tab_partitions where table_owner = '$fowner' and table_name = '$ftable' ;
EOF`
   if [ -z "$var" ];then
       echo 0
   else
     echo "`echo $var | awk '{print $1}'`"
   fi
}
# -------------------------------------------------------------------------------------
. $SBIN/scripts/passwd.env
. ${GET_PASSWD} SYS $ORACLE_SID
if [  "x-$CONNECT_STRING" = "x-" ];then
   echo "could no get a the password of $S_USER"
   exit 0
fi
# -------------------------------------------------------------------------------------
# -------------------------------     Main     i---------------------------------------
# -------------------------------------------------------------------------------------

# ................................................
# LOCK/UNLOCK stats
# ................................................
if [ "$LOCK" = "TRUE" ];then
  
  if [ -z "$ftable" ];then
      SQL=" prompt doing exec dbms_stats.${UN}LOCK_SCHEMA_STATS ('$fowner');;
            exec dbms_stats.${UN}LOCK_SCHEMA_STATS ('$fowner');"
  else
     SQL="prompt doing exec dbms_stats.${UN}LOCK_TABLE_STATS ('$fowner','$ftable');;
      exec dbms_stats.${UN}LOCK_TABLE_STATS ('$fowner','$ftable');"
   fi
sqlplus -s "$CONNECT_STRING" <<EOF
$SQL
EOF
exit
fi
# ................................................
# List existing stats tables in DB or per scheman
# ................................................
if [ "$CHOICE" = "LIST_PARAM" ];then
     sqlplus -s "$CONNECT_STRING"<<EOF
     set pagesize 0 feed on head on pause off
col parameter format a20
col value format a40
set head on
select 'method_opt' parameter, dbms_stats.get_param('method_opt')  value from dual
union
select 'cascade' parameter, dbms_stats.get_param('cascade') value from dual
union
select 'estimate_percent' parameter, dbms_stats.get_param('estimate_percent') value from dual
union
select 'degree' parameter ,dbms_stats.get_param('degree') value from dual
union
select 'no_invalidate' parameter ,dbms_stats.get_param('no_invalidate') value from dual
union
select 'granularity' parameter ,dbms_stats.get_param('granularity') value from dual
union
select 'autostats_target' parameter ,dbms_stats.get_param('autostats_target')value from dual ;
EOF
exit
# ................................................
# Gather/delete system statistics
# ................................................
elif [ "$CHOICE" = "GSYS" ];then
    if [ "$GET" = "TRUE" ];then
            VAR0=${MODE:-noworkload}
            VAR="gathering_mode=>'$VAR0'"
            if  [ -n "$INT_MINUTES" ];then
                 VAR="$VAR, interval=> $INT_MINUTES"
            fi
            SQL="Prompt doing exec dbms_stats.gather_system_stats($VAR);
            exec dbms_stats.gather_system_stats($VAR);"
    else
            SQL="Prompt doing exec dbms_stats.delete_system_stats();
exec dbms_stats.delete_system_stats();"
      fi
    echo "$SQL"
    if [ "$EXECUTE" = TRUE ];then
sqlplus -s "$CONNECT_STRING" <<EOF
$SQL
EOF
    fi
    exit
# ................................................
# Gather/delete stats tables on SYS tables
# ................................................
elif [ "$CHOICE" = "GFIX" ];then
      if [ "$GET" = "TRUE" ];then
            SQL="Prompt doing exec dbms_stats.gather_fixed_objects_stats(NULL);
exec dbms_stats.gather_fixed_objects_stats();
prompt List stat from table owned by SYS:
select distinct nvl(to_char(trunc(last_analyzed)),'No stats')Date_taken, count(*)
from dba_tables where owner='SYS' group by trunc(last_analyzed) order by 1 desc;
"
      else
            SQL="Prompt doing exec dbms_stats.delete_fixed_objects_stats();
exec dbms_stats.delete_fixed_objects_stats();
prompt List stat from table owned by SYS:
select distinct nvl(to_char(trunc(last_analyzed)),'No stats')Date_taken, count(*)
from dba_tables where owner='SYS' group by trunc(last_analyzed) order by 1 desc; "
      fi
    if [ "$EXECUTE" = TRUE ];then
sqlplus -s "$CONNECT_STRING" <<EOF
$SQL
EOF
    else
      echo "$SQL"
    fi
    exit
fi

# if owner is not given, deduce it as if the table is unique in DB otherwise output err and exit
if [ -z "$fowner" -a ! "$LIST_STAT" = "TRUE" ];then
   # we use one sql for index and table
   if [ -n "$ftable" ];then
   fvar=`sqlplus -s "$CONNECT_STRING"<<EOF
        set pagesize 0 feed off head off pause off
        select  owner from dba_tables where table_name = '$ftable';
EOF`
  elif [ -n "$findex" ];then
   fvar=`sqlplus -s "$CONNECT_STRING"<<EOF
        set pagesize 0 feed off head off pause off
        select  owner from dba_indexes where index_name = '$findex';
EOF`
   fi
   if [ -z "$fvar" ];then
      echo "could not determine the owner of the object, please use -u <OWNER>"
      exit
   fi
   nbr=`echo $fvar | wc -w`
   if [ ! $nbr -eq 1 ];then
        echo "Mutilple user (\"`echo $var | tr '\n' ' '`\") or non existent table ==> abort"
        echo "user -u <owner>"
        exit
   else
        fowner=`echo $fvar | awk '{print $1}'`
        if [ -z $fowner ];then
            echo "Owner is blank"
            exit 1
        fi
   fi
fi
if [ -z "$l_fstatid" ];then
   var=`echo $ftable | cut -c1-12`
   fstatid=$var$fdate
else
   fstatid=$l_fstatid
   AND_STATID=" and statid='$l_fstatid' "
fi
fstattab=${l_fstattab:-sm_stattab}
fstat_owner=${fstat_owner:-$fowner}
fpart=${l_part:-NULL}
FOUT=$SBIN/tmp/gather_tbl_stats_${ftable}_${fowner}.log
FIL_EXECUTE=$SBIN/tmp/sta_$fstab_$fowner.sql
if [ "$EXECUTE" = "TRUE" ];then
    > $FIL_EXECUTE
fi
$SETXV
# ....................................
# Create the stattab table is required
# ....................................
if [ "$CREATE_STATTAB" = "TRUE" ];then
     sqlplus -s "$CONNECT_STRING"<<EOF
     set pagesize 0 feed on head on pause off
     prompt Doing: dbms_stats.create_stat_table( '$fowner','$fstattab' $TBS) ;
     execute dbms_stats.create_stat_table( '$fowner','$fstattab' $TBS) ;
EOF
    exit
fi

# ................................................
# List existing stats tables in DB or per scheman
# ................................................
if [ "$LIST_STAT" = "TRUE" ];then
     if [ -n "$fowner" ];then
         FOWNER=" and a.owner='$fowner'"
     fi
     if [ -z "$l_fstattab" ];then
        sqlplus -s "$CONNECT_STRING" <<EOF
ttitle skip 2 'MACHINE $HOST - ORACLE_SID : $ORACLE_SID '  right 'Page:' format 999 sql.pno skip 2
column nline newline
set pagesize 66 linesize 100 termout on pause off embedded on verify off heading off
spool $FOUT

select 'Date              -  '||to_char(sysdate,'Day Ddth Month YYYY  HH24:MI:SS'),
       'Username          -  '||USER  nline,
       'List of table stats: for owner ${fowner:-ALL}' nline
from sys.dual
/
          set linesize 124 pagesize 64 feed on head on pause off
          select a.owner,a.table_name
               from all_tab_columns a, all_tab_columns b , all_tab_columns c
           where
               a.owner = b.owner and a.owner = c.owner and a.table_name = b.table_name and
               a.table_name = c.table_name and a.column_name='STATID'  and b.column_name='CH1' and
               c.column_name='FLAGS' $FOWNER
/
EOF
    else
        sqlplus -s "$CONNECT_STRING" <<EOF
ttitle skip 2 'MACHINE $HOST - ORACLE_SID : $ORACLE_SID '  right 'Page:' format 999 sql.pno skip 2
column nline newline
set pagesize 66 linesize 100 termout on pause off embedded on verify off heading off
spool $FOUT

select 'Date              -  '||to_char(sysdate,'Day Ddth Month YYYY  HH24:MI:SS'),
       'Username          -  '||USER  nline,
       'Number of type of stats available in $fstattab per object' nline
from sys.dual
/
          set linesize 124
          set pagesize 64 feed on head on pause off
          col owner format A20
          col statid format A20
          col sub  format 999999 head ' Sub |Partition' justify c
          col part format 999999 head 'Partition'
          col col format 999999 head 'Columns'
          col global format 9999 head 'Glob'
          break on owner on  statid
          prompt .         T=table         I=Index
          prompt
          select a.c5 owner, a.statid,  a.c1 object, a.type ,
                 (select count(rownum) from $fowner.$fstattab where
                           nvl(statid,1)=nvl(a.statid,1) and c5=a.c5 and c1=a.c1 and c2 is null and c3 is null and c4 is null ) Global ,
                 (select count(rownum) from $fowner.$fstattab where
                           statid=a.statid and c5=a.c5 and c1=a.c1 and c2 is not null and c3 is null
                           and c4 is null ) part,
                 (select count(rownum) from $fowner.$fstattab where
                           statid=a.statid and c5=a.c5 and c1=a.c1 and c2 is not null and c3 is not null
                           and c4 is null) Sub ,
                 (select count(rownum) from $fowner.$fstattab where
                           statid=a.statid and c5=a.c5 and c1=a.c1 and c2 is not null and c3 is not null
                           and c4 is not null) col
          from  $fowner.$fstattab a where C4 is null $AND_STATID
               group by a.c5, a.statid, a.c1, a.type
               order by statid,a.type desc
/
EOF
    fi
    exit
fi

if [  "$COPY_ST_STTAB" = "TRUE" ];then
    COPY_ST_STTAB=" ,stattab=>'$fstattab', statid=>'$fstatid', statown=>'$fstat_owner'"
else
   unset COPY_ST_STTAB
fi
# ....................................
#   Delete STATS
# ....................................
if [ "$fdel" = "TRUE" ];then
     # we don't want a default created statid if not statid is requested
     if [ -n "$l_fstattab" ];then
           ST_STATTAB=",stattab=>'$l_fstattab'"
     fi
     if [ -n "$l_fstatid" ];then
        ST_STATID=",statid=>'$l_fstatid'"
     fi
     if [ "$fcasc" = "TRUE" ];then
        DEL_CASC_IDX=",cascade_indexes => TRUE "
     fi
     if [ "$STAT_SYSTEM" = "TRUE" ];then
        PROC=system
        unset DEL_CASC_IDX
        unset DEL_CASC_COL
        unset DEL_CASC_PART
     elif [ -n "$ftable" ];then
        if [ -n "$COL_LIST" ];then
           PROC=column
        else
           PROC=table
        fi
        ST_OWNER=",ownname=>'$fowner'"
        ST_OBJ=",tabname=>'$ftable'"
     elif [ -n "$findex" ];then
        PROC=index
        ST_OBJ=",indname=>'$findex'"
        ST_OWNER=",ownname=>'$fowner'"
        unset DEL_CASC_IDX
        unset DEL_CASC_COL
     elif [ -n "$user" ];then
       PROC=schema
        unset DEL_CASC_IDX
        unset DEL_CASC_COL
        unset DEL_CASC_PART
     fi
     SQL="exec dbms_stats.delete_${PROC}_stats ( statown=>'$fstat_owner' $ST_STATTAB $ST_OWNER $ST_OBJ $DEL_CASC_COL $DEL_CASC_IDX $DEL_CASC_PART $ST_STATID ) ; "
     echo $SQL
     if [ "$EXECUTE" = "TRUE" ];then
        echo $SQL >> $FIL_EXECUTE
        do_execute
     fi
     exit
fi
#pol
# ....................................
#    Export/import STATS
# ....................................
if [ "$EXPORT_STAT" = "TRUE" -o "$IMPORT_STAT" = "TRUE" ];then
#set -x
   if [ "$EXPORT_STAT" = "TRUE" ];then
      IMXP=export
   else
      IMXP=import
   fi
   if [ "$fpart" = "NULL" ];then
      ST_PART=",partname=>NULL"
   else
      ST_PART=",partname=>'$fpart'"
   fi
   if [ "$STAT_SYSTEM" = "TRUE" ];then
      PROC=${IMXP}_system_stats            # import_system_stats or export_system_stats
   elif [ -n "$ftable" ];then
      G_FTABLE="tabname=> '$ftable' ,"
      G_CASCADE="cascade=> $fcasc ,"
      G_OWNER="ownname=>'$fowner' ,"
      PROC=${IMXP}_table_stats
     
      SQL="exec dbms_stats.${IMXP}_table_stats( ownname=>'$fowner', tabname=> '$ftable' $ST_PART, cascade=> $fcasc  $METHOD_OPT $COPY_ST_STTAB) "
      echo "Doing $SQL"
      sqlplus -s "$CONNECT_STRING"<<EOF
           $SQL
EOF
      cpt=`get_tab_nbr_part`
      if [ $cpt -gt -1 -a -z "$l_part" ];then
         PART_NAME=`ret_part PART_NAME $cpt TAB`
         DBA_TAB=`ret_part DBA_TAB $cpt TAB`
         LST_PART=`sqlplus -s "$CONNECT_STRING"<<EOF
         set pagesize 0 feed off head off pause off
         select $PART_NAME from $DBA_TAB where table_owner = '$fowner' and table_name = '$ftable' order by 1;
EOF`
         typeset -u fpart
         for fpart in `echo $LST_PART`
         do
           SQL="exec dbms_stats.${IMXP}_table_stats( ownname=>'$fowner', tabname=> '$ftable', partname=> '$fpart', cascade=> $fcasc $METHOD_OPT $COPY_ST_STTAB) "
           echo "Doing $SQL"
           sqlplus -s "$CONNECT_STRING"<<EOF
alter session set events '10046 trace name context forever,level 12' ;
           $SQL
EOF
exit
           unset SQL
         done
      fi

   elif [ -n "$findex" ];then
      G_OWNER="ownname=>'$fowner' ,"
      G_FTABLE=", indname=> '$ftable' ,"
      unset G_CASCADE
      PROC=${IMXP}_index_stats
      SQL="exec dbms_stats.${IMXP}_index_stats( ownname=>'$fowner', indname=> '$findex' $ST_PART $COPY_ST_STTAB) "
      echo "Doing $SQL"
      sqlplus -s "$CONNECT_STRING"<<EOF
           $SQL
EOF
      cpt=`get_ind_nbr_part`
      if [ "$cpt" -gt -1 -a -z "$l_part" ];then
         PART_NAME=`ret_part PART_NAME $cpt IND`
         DBA_TAB=`ret_part DBA_TAB $cpt IND`
         LST_PART=`sqlplus -s "$CONNECT_STRING"<<EOF
         set pagesize 0 feed off head off pause off
         select $PART_NAME from $DBA_TAB where index_owner = '$fowner' and index_name = '$findex' order by 1;
EOF`
         typeset -u fpart
         for fpart in `echo $LST_PART`
         do
           SQL="exec dbms_stats.${IMXP}_index_stats( ownname=>'$fowner', indname=> '$findex', partname=> '$fpart' $COPY_ST_STTAB) "
           echo "Doing $SQL"
           sqlplus -s "$CONNECT_STRING"<<EOF
           $SQL
EOF
           unset SQL
         done
      fi
   elif [ -n "$fowner" ];then
      G_OWNER="ownname=>'$fowner' ,"
      PROC=${IMXP}_schema_stats
      SQL="exec dbms_stats.$PROC( $G_OWNER $G_FTABLE $G_CASCADE $COPY_ST_STTAB) "


      echo "Doing $SQL"
      sqlplus -s "$CONNECT_STRING" <<EOF
      $SQL
EOF
   else
      echo "Error in input selection ==> abort"
   fi

  exit
fi
# ********************************************************
# ********************************************************
#  Gather stats for TABLE,INDEX or SCHEMA
# ********************************************************
#
# ********************************************************
# A) Gather stats for TABLE
# ********************************************************
if [ -n "$ftable" ];then
  #set -x
  # ....................................
  # check if we will gather stats only
  # for some columns
  # ....................................
  if [ -n "$COL_LIST" ];then
     METHOD_OPT=", method_opt => 'For columns ${COL_LIST}',"
  fi
  # ....................................
  # check if table is not partitioned :
  # ....................................
  var=`sqlplus -s "$CONNECT_STRING"<<EOF
     set pagesize 0 feed off head off pause off
     select  partitioned from dba_tables where owner = '$fowner' and table_name = '$ftable';
EOF`

  # ....................................
  # Partition of sub partition?
  # ....................................
  if [ "$var" = "YES" -a ! "$fgran" = "GLOBAL" -a ! "$fgran" = "ALL" -a ! "$fgran" = "DEFAULT" -a -z "$l_part" ];then
     part_cpt=`get_tab_nbr_part`
     PART_NAME=`ret_part PART_NAME $part_cpt TAB`
     DBA_TAB=`ret_part DBA_TAB $part_cpt TAB`
     # .............................................
     # loop on partitions now to issue the statement
     # .............................................
     LST_PART=`sqlplus -s "$CONNECT_STRING"<<EOF
       set pagesize 0 feed off head off pause off
       select $PART_NAME from $DBA_TAB where table_owner = '$fowner' and table_name = '$ftable' order by 1;
EOF`
     typeset -u fpart

     for fpart in `echo $LST_PART`
     do
          SQL="exec dbms_stats.gather_table_stats( ownname=>'$fowner',
                       tabname=> '$ftable',
                       partname=> '$fpart'  
                       degree=> $fdegree,
                       estimate_percent=> $fperc,
                       granularity=>'$fgran',
                       cascade=>$fcasc  $COPY_ST_STTAB $METHOD_OPT) "
        echo $SQL
        if [ "$EXECUTE" = "TRUE" ];then
           echo $SQL >> $FIL_EXECUTE
        fi
     done

  else
     # .............................................
     # No partitions or single (sub)pationtion
     # .............................................
     if [ -n "$l_part" ];then
         ST_PARTNAME="partname=> '$l_part',"
     fi
     SQL="exec dbms_stats.gather_table_stats( ownname=>'$fowner',
                         tabname=> '$ftable'
                         Degree=> $fdegree,  $ST_PARTNAME
                         estimate_percent=> $fperc,
                         granularity=>'$fgran',
                         cascade=>$fcasc $COPY_ST_STTAB $METHOD_OPT) "
     echo $SQL
     if [ "$EXECUTE" = "TRUE" ];then
          echo $SQL >> $FIL_EXECUTE
     fi
   fi

  # ********************************************************
  # B) INDEX
  # ********************************************************

  elif [ -n "$findex" ];then # index
  # ....................................
  # check if Index is not partitioned :
  # ....................................
  var=`sqlplus -s "$CONNECT_STRING"<<EOF
     set pagesize 0 feed off head off pause off
     select  partitioned from dba_indexes where owner = '$fowner' and index_name = '$findex';
EOF`

  # ....................................
  # Partition of sub partition?
  # ....................................
  if [ "$var" = "YES" -a ! "$fgran" = "GLOBAL" -a !  "$fgran" = "ALL" -a ! "$fgran" = "DEFAULT" -a -z "$l_part" ];then
     cpt=`get_ind_nbr_part`

     # you can have subpartitions but not necessarily ask sta on subpartitions. so we test on fgran
     # and no specific partition were mentioned
     if [ $cpt -gt -1  -a -z "$l_part" -a "$fgran" = "SUBPARTITION" ];then
        PART_NAME=`ret_part PART_NAME $cpt IND`
        DBA_TAB=`ret_part DBA_TAB $cpt IND`
        if [ "$fgran" = "ALL" -o "$fgran" = "SUBPARTITION" ];then
           :
        else
           echo "Garther stats for subpartitions are only done for Granularity = \"ALL\" or \"SUBPARTITION\""
           echo "If you are not happy with that, you can send your instults at ceo@Oracle.com"
           echo "Aborting ==>"
           exit 0
        fi
     else
        PART_NAME=PARTITION_NAME
        DBA_TAB=DBA_IND_PARTITIONS
     fi
     # .............................................
     # loop on partitions now to issue the statement
     # .............................................
     LST_PART=`sqlplus -s "$CONNECT_STRING"<<EOF
     set pagesize 0 feed off head off pause off
     select $PART_NAME from $DBA_TAB where index_owner = '$fowner' and index_name = '$findex' order by 1;
EOF`
     typeset -u fpart
     for fpart in `echo $LST_PART`
     do
       SQL="exec dbms_stats.gather_index_stats( ownname=>'$fowner',
                    indname=> '$findex',
                    partname=> '$fpart',
                    estimate_percent=> $fperc $COPY_ST_STTAB) "
        echo $SQL
        if [ "$EXECUTE" = "TRUE" ];then
            echo $SQL >> $FIL_EXECUTE
        fi
     done

  else

     # .............................................
     # No partitions
     # .............................................
     if [ -n "$l_part" ];then
         ST_PARTNAME="partname=> '$l_part',"
     fi
     SQL="exec dbms_stats.gather_index_stats( ownname=>'$fowner',
                         indname=> '$findex', $ST_PARTNAME
                         estimate_percent=> $fperc $COPY_ST_STTAB) "
   echo $SQL
    if [ "$EXECUTE" = "TRUE" ];then
       echo $SQL >> $FIL_EXECUTE
    fi
 fi
else
     SQL="exec dbms_stats.gather_schema_stats( ownname=>'$fowner',
                         Degree=> $fdegree,
                         estimate_percent=> $fperc $COPY_ST_STTAB) "
     echo $SQL
     if [ "$EXECUTE" = "TRUE" ];then
       echo $SQL >> $FIL_EXECUTE
    fi

fi



  if [ "$EXECUTE" = "TRUE" ];then
     do_execute
  fi