#!/usr/bin/ksh
# author :  B. Polarski
# 20 September 2005

HOST=`hostname`
HOST=`echo $HOST | awk '{ printf ("%-+15.15s",$1)  }'`
# ----------------------------------------------------
function get_ftype
{
if [ -n "$fpart" ];then   
ftype=`sqlplus -s "$CONNECT_STRING" <<EOF
set feed off pagesize 0 head off
select 'TABLE PARTITION' ftype from dba_tab_partitions
        where table_name = upper('$ftable') and table_owner=upper('$fowner') and PARTITION_NAME=upper('$fpart')
union
select 'TABLE SUBPARTITION' ftype from dba_tab_subpartitions
         where table_name = upper('$ftable') and table_owner=upper('$fowner') and SUBPARTITION_NAME=upper('$fpart')
union
select 'LOB PARTITION' ftype from dba_lob_partitions
        where table_name = upper('$ftable') and table_owner=upper('$fowner') and LOB_PARTITION_NAME=upper('$fpart')
union
select 'LOB SUBPARTITION' ftype from dba_lob_subpartitions
         where table_name = upper('$ftable') and table_owner=upper('$fowner') and LOB_SUBPARTITION_NAME=upper('$fpart');
EOF`

else # fpart is not given, we search the table type
ftype=`sqlplus -s "$CONNECT_STRING" <<EOF
set feed off pagesize 0 head off
select ftype from (
select 2 a, 'TABLE PARTITION' ftype from dba_tab_partitions
        where table_name = upper('$ftable') and table_owner=upper('$fowner') 
union
select 1 a,'TABLE SUBPARTITION' ftype from dba_tab_subpartitions
         where table_name = upper('$ftable') and table_owner=upper('$fowner')
union
select 4 a,'LOB PARTITION' ftype from dba_lob_partitions
        where lob_name = upper('$ftable') and table_owner=upper('$fowner')
union
select 3 a,'LOB SUBPARTITION' ftype from dba_lob_subpartitions
         where lob_name = upper('$ftable') and table_owner=upper('$fowner')
order by a 
) where rownum=1;
EOF`
echo $ftype
fi
}
# ----------------------------------------------------
function help
{
more <<EOF

           tbl -u <owner> -t <table_name>                           tbl -ext
           tbl -u <owner> -t <table_name> -ldl                      tbl -u <owner> -ext <table_name> -drop
           tbl -u <owner> -t <table_name>  -p  -d                   tbl -u <owner> -t <table_name> -ddl      # get the ddl
           tbl -u <owner> -t <table_name>  -c  -d                   tbl -u <owner> -t <table_name>  -pred    # list table predicate usage
           tbl -u <owner> -t <table_name>  -a  -d -i                tbl -u <owner> -cd <CONSTRAINT TYPE : ie 'P'> -x       
           tbl -u <owner> -t <table_name>  -s                       tbl -u <owner> -log|-logc | -logu
           tbl -u <owner> -t <table_name>  -l                       tbl -t <table_name>  -lbs <lob column_name>
           tbl -u <owner> -cl <CONSTRAINT TYPE : ie 'P'>            tbl -u <owner> -t <table_name> -noidxfk
           tbl -u <owner> -ce <CONSTRAINT TYPE : ie 'P'> -x         tbl -n <table_name> (Add a % before, after or at both end) to the part name
           tbl -txn -t <table_name> -u < OWNER> [ -rn <nn> ]        tbl -t <table_name>  -spc|-uspc  -part <PARTITION_NAME>  [-u <OWNER>]
           tbl -g -u <OWNER> -t <table_name>                        tbl [-u <owner>] -t <table_name> -ch <COLUMN_NAME>

           add to -t :
           ==============
                 -u  limit to owner                                -a  Chained rows                
                 -c  Constraints                                   -d  Table description
                 -i  additional info on  table                   -lob  show lobs info
                 -p  (sub)partitions                               -s  List stats info gathered on columns
               -ldl  display last ddl applied on the table       -ddl  extract ddl of table
              -drop  drop external table                         -lbs  List lob size distrubtion              
               -spc  List space map using dbms_space.space_usage -uspc List unused space using dbms_space.unused_space
               -dep  List all dependent segemnts                 -txn  List transaction available in flash.
              -pred  show predicate usage;                       -ord <n> # values are 3 to 7 and represent columns
                -g   List table columns with histogram on them    -ch <COLUMN_NAME> List histograms on COLUMN_NAME
                -cl  List shema Constraints. Constraint type is the letter representing the type of the constraint.  type 'ALL' to see all constraintstype
                -cd  Generate script to Disable shema|table Constraints. Constraint type is the letter representing the type of the constraint.  
                     type 'ALL' affect all constraintstype. If you omit table and leave only schema, then all constraints are taken in account
                -ce  Generate script to Enable shema|table Constraints. Constraint type is the letter representing 
                     the type of the constraint.  type 'ALL' affect all constraintstype. If you omit table and leave 
                     only schema, then all constraint are taken in account
          -truncate  Generate script to truncate all tables in a given schema. '-x' option will not execute this.  
               -ext  List all  externamal tables; drop it if -t <table> -drop is added
           -noidxfk  List Foreign key without index and the Parent table name and columns
                 -n  list all tables whose name is like %xxx%. You need to provide the % yourself : ie: tlb -u soe -n MV_%

               -log  Supplemental log groups    -logc  supplemental column       -logu  supplemental column in table that without FK, PK or BITMAP 

      Also you can use : 'idx -t <table_name>' to list associated indexes.  It is intentional that -drop only access external table. 
     If you want to drop a table, connect into db.  The time it takes gives you a chance to realize what you do.

     Example: 

          tbl -t EMP -u SCOTT -p           # List all partitions
          tbl -t EMP -u SCOTT -g           # display all colums of table EMP with histogram on them 
          tbl -t EMP -u SCOTT -ch DEPNO    # display the histogram on column DEPNO
EOF

exit
}
# ----------------------------------------------------
if [ -z "$1" ];then
  help
fi
PROMPT="
prompt .          tbl -h for extended help
prompt .          use : idx -t <table_name> to list associated indexes of this table
"

ALTERNATE_FIELD="b.num_rows snum,"
NUM_ROWS=30
desc=FALSE
AND=" and "
typeset -u ftable
typeset -u fowner
typeset -u CONS_TYPE
while [ -n "$1" ]
do
  case "$1" in
       -a )  req=chain  ;;
       -c )  req=constraints  ;;
      -ce )  req=enable_constraints  ; CONS_TYPE=$2; shift ;;
      -cd )  req=disable_constraints  ; CONS_TYPE=$2; shift ;;
      -ch )  req=histogram;COL_NAME=$2; shift ;;
      -cl )  req=list_constraints  ; CONS_TYPE=$2; shift ;;
       -d )  desc=TRUE ;;
     -dep )  req=DEP ;;
     -ext )  EXTERNAL=TRUE
             if [ -n "$2" ]; then
                ftable=$2
                shift
             fi;;
     -ldl )  SQL_DDL=TRUE ;;
     -ddl )  req=ddl ;;
    -drop )  DROP=TRUE ;;
       -g )  req=list_hist_col ;;
       -i )  ADDITIONAL_INFO=TRUE ;;
     -lbs )  req=dis ; COL=$2; shift;;
     -lob )  req=lobs;;
     -log )  req=LOG_GROUP ;;
    -logc )  req=LOG_COL ;;
    -logu )  req=LOG_NO_KEY ;;
       -n )  ftable=$2 ; TABLE_LIKE=" TABLE_NAME like '$ftable'" ; OBJECT_LIKE=" and OBJECT_NAME = '$ftable'" ; shift;;
 -noidxfk )  req=noidxfk ;;
     -ord )  ORD_PRED=$2; shift ;;
       -p )  req=part ;;
    -part )  fpart=$2 ; shift ;;
      -ph )  req=part_hv ;;
    -pred )  req=predicate_usage  ;;
      -rn )  NUM_ROWS=$2 ; shift ;;
       -s )  req=stats  ;;
     -spc )  req=SPACE_USAGE ;;
       -t )  ftable=$2
             TABLE_LIKE=" TABLE_NAME = '$ftable'"
             AND_TABLE=" and TABLE_NAME = upper('$ftable') "
             OBJECT_LIKE=" and OBJECT_NAME = '$ftable'"
             shift;;
     -txn )  EXECUTE=YES; req=TXN ;;
-truncate )  req="truncate" ;;
       -u )  fowner=$2 ; FOWNER="owner = '$fowner' " ; AND_FOWNER=" and  $FOWNER" ; shift;;
    -uspc )  req=UNUSED_SPACE ;;
       -v )  SETXV="set -x" ;;
       -x )  EXECUTE="YES" ;;
       -h )  help ; exit ;;
       -w )  ALTERNATE_FIELD="(nvl(b.blocks,a.blocks) * &fsize)/1024/1024 fsize," ;;
        * ) echo "Invalid parameter $1" ;help ; exit ;;
  esac
  shift
done
FOUT=$SBIN/tmp/tbl_${ORACLE_SID}_${fowner}_${ftable}.log

if [ -n "$SQL_DDL" ];then
     if [ -n "$FOWNER" ];then
       AND=" and "
     else
       unset AND
     fi
     SQL_DDL=" select object_name table_name, owner, to_char(created,'MM/DD/YYYY HH24:MI:SS') Created,
               to_char(last_ddl_time, 'MM/DD/YYYY HH24:MI:SS') last_ddl_time, temporary, object_id, data_object_id
                  from dba_objects where object_type = 'TABLE' $AND_FOWNER $OBJECT_LIKE order by 2;"
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
# --------------------------------------------------------------------------

if [ "$EXTERNAL" = "TRUE"  ];then
   FIELD_LIST="a.owner||'.'|| a.table_name tbl ,  a.DIRECTORY_NAME, b.directory_path||'/'||a.location fil"
   if [ -n "$ftable" ];then
      if [ "$DROP" = "TRUE" ];then
         SQL="DROP TABLE $ftable ;"
         sqlplus -s "$CONNECT_STRING" <<EOF
         $SQL
EOF
      exit
      else
         SQL="select $FIELDS_LIST from dba_external_locations a, dba_directories b
               where table_name = 'ftable' and a.DIRECTORY_NAME = b.DIRECTORY_NAME"
      fi
   else
      SQL="select $FIELD_LIST from dba_external_locations a, dba_directories b
                  where a.DIRECTORY_NAME = b.DIRECTORY_NAME"
   fi
   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,
       'List external table(s)' nline
from sys.dual
/
prompt

set head on PAGESIZE 55
set linesize 124
col fil format A76 head "File path"
col directory_name format A18
col tbl format A28 head "Owner|Table name"
select tbl,directory_name,fil from ($SQL)
/
EOF
   exit
fi

# we don't perfom an external table operation

$SETXV
if [ "$desc" = "TRUE" -a -n "$ftable" ];then
    # we take only the first one
    if [ -n "$fowner" ];then
    sqlplus -s "$CONNECT_STRING" <<EOF
desc $fowner.$ftable
exit
EOF
else
    sqlplus -s "$CONNECT_STRING" <<EOF
col owner new_value owner
select owner from dba_tables where table_name = '$ftable' and rownum = 1 ;
desc "&owner"."$ftable"
exit
EOF
    fi
fi

if [ -n "$ftable" -a -z "$fowner" ];then
ret=`sqlplus -s "$CONNECT_STRING" <<EOF
set feed off pagesize 0 head off
select sum(cpt) from (
select count(1) cpt from dba_tables where table_name=upper('$ftable')
union
select count(*) cpt from dba_lobs where  SEGMENT_NAME=upper('$ftable'));
EOF`
if [ -z "$ret" ];then
  echo "Currently, there is no entry in dba_tables for $ftable"
  exit
elif [ "$ret" -eq "0" ];then
  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 (
select owner from dba_tables where table_name=upper('$ftable')
union
select owner from dba_lobs where  SEGMENT_NAME=upper('$ftable'))
where rownum=1 ;
EOF`
     FOWNER="owner = '$fowner' "
     AND_FOWNER=" and  $FOWNER"
elif [ "$ret" -gt "0"  ];then
   if [ -z "$fowner" ];then
    echo " there are many tables for $ftable:"
    echo "\n Use : "
    echo "\n  tbl -t $ftable -u <user> \n"
 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')
union
select owner, segment_name ,'lob segment' from  dba_lobs where  SEGMENT_NAME=upper('$ftable');
EOF
    exit
   fi
fi
fi

# ...........................................................
# List transactions in flashback
# ...........................................................

if [ "$req" = "TXN" ];then
sqlplus -s "$CONNECT_STRING" <<EOF
set pages 66 lines 190
col VERSIONS_OPERATION for a20 head 'Operation'
col VERSIONS_STARTSCN for 999999999999 head 'Start Scn'
col VERSIONS_ENDSCN for 999999999999 head 'End Scn'
col VERSIONS_STARTTIME for a21 head 'Start Time'
col VERSIONS_ENDTIME for a21 head 'End Time'
select * from (
  SELECT     VERSIONS_XID,  VERSIONS_STARTTIME, VERSIONS_ENDTIME, VERSIONS_STARTSCN,VERSIONS_ENDSCN ,VERSIONS_OPERATION
  FROM   $fowner.$ftable  versions BETWEEN TIMESTAMP MINVALUE AND MAXVALUE    
where VERSIONS_XID is not null
    ORDER  BY VERSIONS_STARTTIME desc
) where  rownum < $NUM_ROWS
/
EOF
# ...........................................................
# List dependent segements
# ...........................................................
elif [ "$req" = "DEP" ];then
fpart=${fpar:-NULL}
sqlplus -s "$CONNECT_STRING" <<EOF
set linesize 190 pagesize 140
break on segment_owner on segment_name on segment_type on tablespace_name
col segment_owner format a22
col segment_name format a30
col segment_type format a18
col tablespace_name format a20
col partition_name format a23
col lob_column_name format a17

set serveroutput on

SELECT * FROM (TABLE(dbms_space.object_dependent_segments('$fowner', '$ftable', $fpart, 1)));
EOF
# ...........................................................
# show dbms_space.space_usage facts
# ...........................................................
elif [ "$req" = "UNUSED_SPACE" ];then
   ftype=`get_ftype`
   ftype=${ftype:-TABLE}
   if [ -z "${ftype%%*SUB*}"  ];then
       SUB=SUB
   else
      unset SUB
   fi
echo "\nowner=$fowner Table=$ftable  Partition=$fpart type=$ftype\n"
#cat <<EOF
sqlplus -s "$CONNECT_STRING" <<EOF
set serveroutput on size unlimited
set lines 190

DECLARE
 segown   VARCHAR2(30) := '$fowner';
 segname  VARCHAR2(30) := '$ftable';
 segtype  VARCHAR2(30) := '$ftype';
 partname VARCHAR2(30) := '$fpart';

 totblock NUMBER:=0;
 totbytes NUMBER:=0;
 unusedbl NUMBER:=0;
 unusedby NUMBER:=0;
 lu_ef_id NUMBER:=0;
 lu_eb_id NUMBER:=0;
 lu_block NUMBER:=0; 
 v_totbk  NUMBER:=0;
 v_totby  NUMBER:=0;
 v_unby   NUMBER:=0;
 v_unbk   NUMBER:=0;
BEGIN
  if ( segtype like 'LOB%' ) then
      select lob_name into segname from (
      select lob_name from dba_lob_subpartitions 
              where table_name = upper('$ftable') and table_owner=upper('$fowner') and LOB_SUBPARTITION_NAME=upper('$fpart') 
      union
      select lob_name from dba_lob_partitions 
         where table_name = upper('$ftable') and table_owner=upper('$fowner') and LOB_PARTITION_NAME=upper('$fpart'));
  end if;
  
  dbms_output.put_line('segtype=' || segtype );
  if segtype = 'TABLE ${SUB}PARTITION' then
      for c in (select ${SUB}partition_name part from dba_tab_${SUB}partitions where table_owner = segown and TABLE_NAME = segname )
      loop
          dbms_space.unused_space(segown, segname, segtype, v_totbk, v_totby, v_unbk, v_unby, lu_ef_id, lu_eb_id, lu_block, c.part);

         dbms_output.put_line('partition ' || c.part || ' Total Blocks: ' || TO_CHAR(v_totbk) || ' Total Bytes: ' || TO_CHAR(v_totby)||
                               '  Unused Blocks: ' || TO_CHAR(v_unbk)|| ' Unused Bytess: ' || TO_CHAR(v_unby));


          totblock:=totblock+v_totbk;
          totbytes:=totbytes+v_totby;
          unusedbl:=unusedbl+v_unbk;
          unusedby:=unusedby+v_unby;
      end loop ;
  else
      dbms_space.unused_space(segown, segname, segtype, totblock, totbytes, unusedbl, unusedby, lu_ef_id, lu_eb_id, lu_block, partname);
  end if ;

  dbms_output.put_line('Total Blocks: ' || TO_CHAR(totblock));
  dbms_output.put_line('Total Bytes: ' || TO_CHAR(totbytes));
  dbms_output.put_line('Unused Blocks: ' || TO_CHAR(unusedbl));
  dbms_output.put_line('Unused Bytess: ' || TO_CHAR(unusedby));
  dbms_output.put_line('Last Used Extent File ID: ' || TO_CHAR(lu_ef_id));
  dbms_output.put_line('Last Used Extent Block ID: ' || TO_CHAR(lu_eb_id));
  dbms_output.put_line('Last Used Block: ' || TO_CHAR(lu_block));
END;
/
EOF
# ...........................................................
# show dbms_space.space_usage facts
# ...........................................................
elif [ "$req" = "SPACE_USAGE" ];then

ftype=`get_ftype`
ftype=${ftype:-TABLE}

if [ -z "${ftype%%*SUB*}"  ];then
    SUB=SUB
else
    unset SUB
fi
if [ -z "${ftype%%*LOB*}"  ];then
   TAB=lob
   FTABLE=LOB
   LOB=LOB_
else
   TAB=tab
   FTABLE=TABLE
   unset LOB
fi
echo "\nowner=$fowner Table=$ftable  Partition=$fpart type=$ftype\n"
sqlplus -s "$CONNECT_STRING" <<EOF
set serveroutput on size unlimited 
set lines 190

DECLARE 

 segown   VARCHAR2(30) := '$fowner';
 segname  VARCHAR2(30) := '$ftable';
 segtype  VARCHAR2(30) := '$ftype';
 partname VARCHAR2(30) := '$fpart';

v_unformatted_blocks number:=0; 
vt_unformatted_blocks number:=0; 
v_unformatted_bytes number; 
v_fs1_blocks number:=0; 
vt_fs1_blocks number:=0; 
v_fs1_bytes number; 
v_fs2_blocks number:=0; 
vt_fs2_blocks number:=0; 
v_fs2_bytes number; 
v_fs3_blocks number:=0; 
vt_fs3_blocks number:=0; 
v_fs3_bytes number; 
v_fs4_blocks number:=0; 
vt_fs4_blocks number:=0; 
v_fs4_bytes number:=0; 
v_full_blocks number:=0; 
vt_full_blocks number:=0; 
v_full_bytes number; 
BEGIN 
/* if ( segtype like 'LOB%' ) then
      select lob_name into segname from (
      select lob_name from dba_lob_subpartitions
              where table_name = upper('$ftable') and table_owner=upper('$fowner') and LOB_SUBPARTITION_NAME=upper('$fpart')
      union
      select lob_name from dba_lob_partitions
         where table_name = upper('$ftable') and table_owner=upper('$fowner') and LOB_PARTITION_NAME=upper('$fpart'));
  end if;
*/
  dbms_output.put_line('segtype=' || segtype );
  if segtype = '${FTABLE} ${SUB}PARTITION' then
      for c in (select ${LOB}${SUB}partition_name part from dba_${TAB}_${SUB}partitions where table_owner = segown and ${FTABLE}_NAME = segname )
      loop
          dbms_space.space_usage (segown, segname, segtype, vt_unformatted_blocks, v_unformatted_bytes, vt_fs1_blocks, v_fs1_bytes, vt_fs2_blocks, v_fs2_bytes, vt_fs3_blocks, v_fs3_bytes, vt_fs4_blocks, v_fs4_bytes, vt_full_blocks, v_full_bytes,c.part); 

           dbms_output.put_line('partition ' || c.part || ' Unformatted Blocks = '||vt_unformatted_blocks|| '   fs1=' || to_char(vt_fs1_blocks) 
                                             || '   fs2=' || to_char(vt_fs2_blocks) || '   fs3=' || to_char(vt_fs3_blocks) 
                                             || '   fs4=' || to_char(vt_fs4_blocks) || ' Full Blocks = '||vt_full_blocks);
          v_fs1_blocks :=v_fs1_blocks + vt_fs1_blocks;
          v_fs2_blocks :=v_fs2_blocks + vt_fs2_blocks;
          v_fs3_blocks :=v_fs3_blocks + vt_fs3_blocks;
          v_fs4_blocks :=v_fs4_blocks + vt_fs4_blocks;
          v_unformatted_blocks:=v_unformatted_blocks+vt_unformatted_blocks;
          v_full_blocks:=v_full_blocks+vt_full_blocks;
      end loop ;
  else
  dbms_space.space_usage (segown, segname, segtype, v_unformatted_blocks, v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes, v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes,partname); 
  end if ;
dbms_output.put_line('Unformatted Blocks          = '||v_unformatted_blocks); 
dbms_output.put_line('0-25%   free space  Blocks# = '||v_fs1_blocks); 
dbms_output.put_line('25-50%  free space  Blocks# = '||v_fs2_blocks); 
dbms_output.put_line('50-75%  free space  Blocks# = '||v_fs3_blocks); 
dbms_output.put_line('75-100% free space  Blocks# = '||v_fs4_blocks); 
dbms_output.put_line('Full Blocks                 = '||v_full_blocks); 
dbms_output.put_line('Total blocks                = '||to_char(v_full_blocks+v_fs4_blocks+v_fs3_blocks+v_fs2_blocks+v_fs1_blocks+v_unformatted_blocks));
end; 
/
EOF

# ...........................................................
# show lob size distribution size
# ...........................................................
elif [ "$req" = "dis" ];then
    if [  -z "$ftable" ];then
        echo "I need a table name"
        exit
    fi
sqlplus -s "$CONNECT_STRING" <<EOF
col fsizz head 'Size(k)'
select 
  case 
   when fsiz = -1 then '-'
   when fsiz = 0 then ' 0 - 1k'
   when fsiz = 1 then ' 1 - 2k'
   when fsiz = 2 then ' 2 - 3k'
   when fsiz = 3 then ' 3 - 4k'
   when fsiz = 4 then ' 4 - 5k'
   when fsiz = 5 then ' 5 - 6k'
   when fsiz = 6 then ' 6 - 7k'
   when fsiz = 7 then ' 7 - 8k'
   when fsiz = 8 then ' 8 - 9k'
   when fsiz = 9 then ' 9 - 10k'
   when fsiz = 10 then '10- 20k'
   when fsiz = 20 then '20 - 30k'
   when fsiz = 30 then '30 - 50k'
   when fsiz = 50 then '50 - 100K'
   when fsiz = 100 then '100 - 1000K'
   when fsiz = 1000 then '1 - 10m'
   when fsiz = 10000 then '10m+'
  end fsizz,
  cpt from (
select count(*) cpt, fsiz from
(
select
  case
     when  fsize  > 9999999 then 10000
     when  fsize  between 1000000 and 9999999 then 1000
     when  fsize  between 100000 and 999999 then 100
     when  fsize  between 50000 and 99999 then 50
     when  fsize  between 30000 and 49999 then 30
     when  fsize  between 20000 and 29999 then  20
     when  fsize  between 10000 and  19999 then  10
     when  fsize  between 9000 and 9999 then 9
     when  fsize  between 8000 and 8999 then 8
     when  fsize  between 7000 and 7999 then 7
     when  fsize  between 6000 and 6999 then 6
     when  fsize  between 5000 and 5999 then 5
     when  fsize  between 4000 and 4999 then 4
     when  fsize  between 3000 and 3999 then 3
     when  fsize  between 2000 and 2999 then 2
     when  fsize  between 1000 and 1999 then 1
     when  fsize  between 1 and 999 then 0
     when fsize  = 0 then -1 
  else fsize
  end  fsiz
from (
select ceil(dbms_lob.getlength($COL))fsize from $fowner.$ftable)
) group by fsiz) order by 1 ;
EOF
# ...........................................................
# histogram : show columns historgrams
# ...........................................................
elif [ "$req" = "histogram" ];then
   if [ -n "$FOWNER" ];then
      AND=" and"
   else
      unset AND
   fi
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, 'Show table histogram for $ftable columns $COL_NAME ' nline from sys.dual
/
set linesize 150 head on pagesize 66
col column_name format a25
col ENDPOINT_NUMBER format 99999999999999999 head 'End Point Number'
col ENDPOINT_VALUE head 'End Point Value'
col frequency head 'Frequency'
break on COLUMN_NAME on report
Prompt # .................................................

prompt # This query is only valid if there are no nulls

Prompt # .................................................
prompt

-- select table_name, COLUMN_NAME  , ENDPOINT_NUMBER , ENDPOINT_VALUE , ENDPOINT_ACTUAL_VALUE
-- from DBA_TAB_HISTOGRAMS 
  
select  COLUMN_NAME,
    endpoint_value,   
    endpoint_number,   
    endpoint_number - nvl(prev_number,0) frequency   
from    (   
    select COLUMN_NAME, 
        endpoint_value,   
        endpoint_number,   
        lag(endpoint_number,1) over(   
            order by endpoint_number   
        ) prev_number   
    from  
        dba_tab_histograms   
where 
    table_name = '$ftable' $AND $FOWNER  and COLUMN_NAME = '$COL_NAME'
)
order by ENDPOINT_NUMBER
/
EOF

# ...........................................................
# noidxfk : no index on foreign key
# ...........................................................
elif [ "$req" = "noidxfk" ];then
 unset AND_OWNER
 unset AND_TABLE
 if [ -n "$ftable" ];then
    AND_TABLE=" and acc.table_name = '$ftable' "
 fi
 if [ -n "$fowner" ];then
    AND_OWNER="  and acc.owner = '$fowner' "
 fi
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, 'Show foreign key without index' nline from sys.dual
/
set linesize 155 head on pagesize 66
col tbl0 format a35 head "Child Table"
col tbl1 format a35 head "Parent Table"
col cons0 format a25 head "FK without| index" justify c
col cons1 format a25 head "Parent Table| Cons/index" justify c
col cn format a25 head "FK  Columns"
col position format 99 head "Pos"
break on cons0 on tbl0 on report

select a.constraint_name cons0 ,a.owner||'.'|| a.table_name tbl0, a.column_name cn, a.position, a.r_constraint_name cons1
          , b.owner ||'.' || b.table_name tbl1 from
    (
    SELECT ac.constraint_name,acc.owner, acc.table_name, acc.column_name, acc.position, ac.r_constraint_name
        FROM
           all_cons_columns acc,
           all_constraints ac
        WHERE ac.constraint_name = acc.constraint_name
        and  ac.owner=acc.owner
       AND ac.constraint_type = 'R' $AND_OWNER $AND_TABLE
   MINUS
   SELECT ' ',table_owner, table_name, column_name, column_position,' '
   FROM all_ind_columns
   ) a
   , dba_constraints b
   where
    b.owner = a.owner
   and b.constraint_name =  a.r_constraint_name
  order by  1,5,4,3
/
EOF



# ...........................................................
elif [ "$req" = "truncate" ];then
   TMP_CONS=$SBIN/tmp/truncate_$fowner_$ORACLE_SID.sql
   sqlplus -s "$CONNECT_STRING" <<EOF
spool $TMP_CONS
set pagesize 0 head off linesize 120 trimspool on feed off
select 'truncate table ${fowner}.'|| table_name || ' reuse storage ;' from dba_tables 
         where owner = upper('$fowner') $AND_TABLE  order by table_name
/
EOF

# ...........................................................
# ...........................................................
elif [ "$req" = "LOG_GROUP" ];then
     if [ -n "$fowner" ];then
          WHERE_USER=" where owner = upper('$fowner') "
      elif [ -n "$ftable" ];then
          AND_TABLE=" where table_name = upper('$ftable') "
     fi
    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, 'Show table supplemental log group' nline from sys.dual
/
set head on linesize 132 pagesize 66
break on owner on log_group_name on report
col owner format a20
col LOG_GROUP_NAME format a30
col COLUMN_NAME format a20
select OWNER, LOG_GROUP_NAME, TABLE_NAME, LOG_GROUP_TYPE, ALWAYS, GENERATED from DBA_LOG_GROUPS $WHERE_USER $AND_TABLE
  order by owner,log_group_name,table_name;
EOF

# ...........................................................
# ...........................................................
elif [ "$req" = "LOG_NO_KEY" ];then
     if [ -n "$fowner" ];then
          AND_USER=" and owner = upper('$fowner') "
     elif [ -n "$ftable" ];then
          AND_TABLE=" AND table_name = upper('$ftable') "
     fi

    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, 'Show table column in supplemental log group not derived from PK, FK, BITMAP' nline from sys.dual
/
set head on linesize 132 pagesize 66
break on table_name on report
col COLUMN_NAME format a30
SELECT distinct table_name, log_group_name , column_name FROM all_log_group_columns
   where (table_name, column_name) IN
       (SELECT table_name, column_name
          FROM all_log_group_columns
           where logging_property = 'LOG' 
        MINUS
        SELECT acc.table_name, acc.column_name
          FROM all_constraints t, all_cons_columns acc
           where acc.owner = t.owner
           AND acc.constraint_name = t.constraint_name
           AND t.constraint_type IN ('P','R') 
        MINUS
        SELECT aic.table_name, aic.column_name
          FROM all_indexes t, all_ind_columns aic
           where aic.index_owner = t.owner 
           AND aic.index_name = t.index_name
           AND (t.index_type like '%BITMAP' OR t.uniqueness = 'UNIQUE'))  $AND_USER $AND_TABLE
order by table_name ; 
EOF
elif [ "$req" = "LOG_COL" ];then
     if [ -n "$fowner" ];then
          WHERE_USER=" where owner = upper('$fowner') "
     elif [ -n "$ftable" ];then
          AND_TABLE=" where table_name = upper('$ftable') "
     fi
    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, 'Show table column in supplemental log group' nline from sys.dual
/
set head on linesize 132 pagesize 66
break on owner on log_group_name on report
col owner format a20
col LOG_GROUP_NAME format a30
col COLUMN_NAME format a20
select  OWNER, LOG_GROUP_NAME, TABLE_NAME, COLUMN_NAME, POSITION,LOGGING_PROPERTY from DBA_LOG_GROUP_COLUMNS $WHERE_USER $AND_TABLE;

EOF
# ...........................................................
# ...........................................................
elif [ "$req" = "enable_constraints" ];then
   if [ -z "$fowner" ];then
     echo "I need a constraints owner"
     exit 0
   fi

   if [ "$CONS_TYPE" = "ALL" ];then
      unset AND_TYPE
   else
      AND_TYPE=" and constraint_type = '$CONS_TYPE' "
   fi

   if [ -n "$ftable" ];then
      AND_TABLE=" and table_name = upper('$ftable') "
   else
      unset AND_TABLE
   fi

   TMP_CONS=$SBIN/tmp/ena_const_$fowner_$ORACLE_SID.sql

   sqlplus -s "$CONNECT_STRING" <<EOF
spool $TMP_CONS
set pagesize 0 head off linesize 120 trimspool on feed off
select 'alter table ${fowner}.'|| table_name || ' enable constraint '||constraint_name||';'
from dba_constraints where owner = upper('$fowner') $AND_TABLE $AND_TYPE order by table_name 
/ 
spool off 
EOF

   if [ "$EXECUTE" = "YES" ];then
   sqlplus -s "$CONNECT_STRING" <<EOF
@$TMP_CONS
EOF
   fi
# ...........................................................
# ...........................................................
elif [ "$req" = "disable_constraints" ];then
   if [ -z "$fowner" ];then
     echo "I need a constraints owner"
     exit 0
   fi
   if [ "$CONS_TYPE" = "ALL" ];then
      unset AND_TYPE
   else
      AND_TYPE=" and constraint_type = '$CONS_TYPE' "
   fi
   if [ -n "$ftable" ];then
      AND_TABLE=" and table_name = upper('$ftable') "
   else
      unset AND_TABLE
   fi
   TMP_CONS=$SBIN/tmp/dis_const_$fowner_$ORACLE_SID.sql
   sqlplus -s "$CONNECT_STRING" <<EOF
spool $TMP_CONS
set pagesize 0 head off linesize 120 trimspool on feed off
select 'alter table ${fowner}.'|| table_name || ' disable constraint '||constraint_name||' cascade;' 
from dba_constraints where owner = upper('$fowner') $AND_TABLE $AND_TYPE order by table_name 
/ 
spool off 
EOF
   if [ "$EXECUTE" = "YES" ];then
   sqlplus -s "$CONNECT_STRING" <<EOF
@TMP_CONS
EOF
   fi
# ...........................................................
# ...........................................................
elif [ "$req" = "list_constraints" ];then
   if [ -z "$fowner" ];then
     echo "I need a constraints owner"
     exit 0
   fi
   if [ "$CONS_TYPE" = "ALL" ];then
      unset AND_TYPE
   else
      AND_TYPE=" and constraint_type = '$CONS_TYPE' "
   fi
   if [ -n "$ftable" ];then
      AND_TABLE=" and table_name = upper('$ftable') "
   else
      unset AND_TABLE
   fi
   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 all constraints for schema : $fowner' nline from sys.dual
/
set linesize 150 head on
col r_owner format a18 head "Remote owner"
col R_CONSTRAINT_NAME format a25  head "Remove constraint|Name" justify c
select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS,to_char(LAST_CHANGE,'YYYY-MM-DD HH24:MI'), r_owner, R_CONSTRAINT_NAME 
     from  dba_constraints where OWNER = '$fowner' $AND_TABLE $AND_TYPE order by table_name;
EOF
# ...........................................................
# "predicate_usage"
# ...........................................................
elif [ "$req" = "predicate_usage" ];then
 unset AND_OWNER
 unset AND_TABLE
 if [ -n "$ftable" ];then
    AND_TABLE=" o.name = '$ftable' and"
 fi
 if [ -n "$fowner" ];then
    AND_OWNER=" r.name = '$fowner' and"
 fi
 ORD_PRED=${ORD_PRED:-4}
#cat <<EOF
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, 'Show table/col predicate usage' nline from sys.dual
/
set linesize 132 head on pagesize 33
col obj format a35 head "Table name"
col col1 format a26 head "Column"
col equijoin_preds format 9999999 head "equijoin|Preds" justify c
col nonequijoin_preds format 9999999 head "non|equijoin|Preds" justify c
col range_preds format 999999 head "Range|Pred" justify c
col equality_preds format 9999999 head "Equality|Preds" justify c
col like_preds format 999999 head "Like|Preds" justify c
col null_preds format 999999 head "Null|Preds" justify c
prompt
prompt Order by (-ord <n>) : 3=equality_preds 4=equijoin_preds 5=nonequijoin_preds
prompt .                   : 5=range_preds    6=like_preds     7=null_preds
select r.name ||'.'|| o.name "obj" , c.name "col1",
      equality_preds, equijoin_preds, nonequijoin_preds, range_preds,
      like_preds, null_preds, to_char(timestamp,'DD-MM-YY HH24:MI:SS') "Date"
 from sys.col_usage$ u, sys.obj$ o, sys.col$ c, sys.user$ r
  where o.obj# = u.obj#    and $AND_TABLE $AND_OWNER
        c.obj# = u.obj#    and
        c.col# = u.intcol# and
        o.owner# = r.user# and
       (u.equijoin_preds > 0 or u.nonequijoin_preds > 0)
   order by $ORD_PRED desc
/
EOF
# ...........................................................
# Columns histograms
# ...........................................................
elif [ "$req" = "list_hist_col" ];then
   if [ -n "$FOWNER" ];then
      AND=" and"
   else
      unset AND
   fi
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, 'Show table/col stats' nline from sys.dual
/
set head ON PAGESIZE 55 linesize 190
col selectivity format 9999999999.9 head "Rows per key|(Selectivity)"
col num_buckets format 999999 head " Num  |Bucket"
col dtyp format A16 head "Data Type"
col num_distinct for 99999999 head 'Num|distinct' justify c
col num_nulls head 'Num|Nulls' justify c
col Density head 'Density'
col Histogram head 'Histogram' for a15
col column_name for a22
col column_id for 999 head 'Col|id'
col nullable for a4 head 'Null|able'
$PROMPT
prompt
rem set termout off
with v1 
   as (select num_rows from dba_tables where $FOWNER  $AND  table_name = '$ftable')
select COLUMN_ID, a.column_name, a.data_type dtyp, 
           a.num_nulls, a.num_distinct,
           decode(nvl(a.num_distinct,0),0,0,(num_rows-num_nulls)/a.num_distinct) selectivity, density,
           HISTOGRAM,(select count(1) from dba_tab_histograms where
                       $FOWNER $AND table_name = '$ftable' and column_name=a.column_name) num_buckets, 
           '  '||NULLABLE NULLABLE
    from   dba_tab_columns a, v1 b
   where  $FOWNER  $AND  table_name = '$ftable' and histogram != 'NONE'
   order by column_id
/
EOF

# ...........................................................
# Columns stats
# ...........................................................
elif [ "$req" = "stats" ];then
   if [ -n "$FOWNER" ];then
      AND=" and"
   else
      unset AND
   fi
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, 'Show table/col stats' nline from sys.dual
/
set head ON PAGESIZE 55 linesize 190
col selectivity format 9999999999.9 head "Rows per key|(Selectivity)"
col num_buckets format 999999 head " Num  |Bucket"
col avg_col_len format 999 head "Agv|Col|Len"
col la format A18 head "Last Analysed"
col dtyp format A16 head "Data Type"
col num_rows new_value row_num noprint
col num_distinct for 99999999 head 'Num|distinct' justify c
col global_stats for a4 head 'Glob|Stat'
col num_nulls head 'Num|Nulls' justify c
col Density head 'Density'
col Histogram head 'Histogram' for a12
$PROMPT
prompt
rem set termout off
select num_rows from dba_tables where $FOWNER  $AND  table_name = '$ftable';
rem set termout on

select a.column_name, a.data_type||'('||a.data_length||')' dtyp, a.num_distinct,
           decode(nvl(a.num_distinct,0),0,0,(&row_num-num_nulls)/a.num_distinct) selectivity, density,
           a.global_stats, a.num_nulls,
           HISTOGRAM,(select count(1) from dba_tab_histograms where
                       $FOWNER $AND table_name = '$ftable' and column_name=a.column_name) num_buckets,
           a.avg_col_len, to_char(a.last_analyzed,'DD-MM-YY HH24:MI:SS') la
    from   dba_tab_columns a
   where  $FOWNER  $AND  table_name = '$ftable'
/
EOF

echo '********************************************************************'
echo "log file: $FOUT"
echo '********************************************************************'
echo

# ...........................................................
elif [ "$req" = "part_hv" ];then
 if [ -n "$fowner" ];then
         AND_FOWNER=" and table_owner =  '$fowner' "
 fi
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 190 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, 'Show partitions high values for table: $ftable' from sys.dual
/
set serveroutput on size unlimited lines 190 pagesize 66

declare
   tt varchar2(512);
   loc long;
   function ff ( ll long) return varchar2 is
    var varchar2(512);
   begin
     select ll into var from dual ;
     return var;
   end ff ;
begin
  dbms_output.put_line(rpad('Partion_name',30,' ')|| 'High_value');
  dbms_output.put_line(rpad('-',29,'-')||' '|| rpad('-',70,'-'));
  for  t in (select partition_name,high_value from dba_tab_partitions where table_name = '$ftable' $AND_FOWNER )
  loop
    tt:= ff(t.high_value) ;
    dbms_output.put_line(rpad(t.partition_name,30,' ') || tt );
  end loop;
end;
/
EOF
# ...........................................................
elif [ "$req" = "part" ];then
# -p : list partitions

   if [ -n "$fowner" ];then
         AND_FOWNER=" and a.table_owner =  '$fowner' "
   fi
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 190 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, 'Show partitions for table: $ftable' from sys.dual
$PROMPT
/
col db_block_size new_value fsize noprint
set head off feed off
select value db_block_size from v\$parameter where name = 'db_block_size'
/
set head on
break on partition_position on partition_name on pnum on report
COL partition_position       FORMAT  9999 heading 'Part| Pos'
COL fsize                    FORMAT  9999999   heading 'Size (m)'
COL snum                     FORMAT  99999999   heading 'Subpart|Num rows'
COL pnum                     FORMAT  A12   heading 'Partition|Num rows'
COL partition_name           FORMAT  A23 head 'Partition name'
COL subpartition_name        FORMAT  A25 head 'Subartition name'
COL tablespace_name          FORMAT  A25   justify c HEAD ' Tablespace name'
col last_analyzed            format  A17  head 'Last Analyzed'
comp sum of fsize on report
prompt The '+' signal aggregate stats : means stats derived from others stats
prompt
select a.partition_position, a.partition_name,
          decode(a.global_stats,'YES',to_char(a.num_rows),
            'NO', '+'||to_char(round(a.num_rows,0)),to_char(round(a.num_rows,0))) pnum,
       b.subpartition_name,
       nvl(b.tablespace_name,a.tablespace_name) tablespace_name, $ALTERNATE_FIELD
       to_char(nvl(b.last_analyzed,a.last_analyzed),'DD-MM-YY HH24:MI:SS') last_analyzed
   from dba_tab_partitions a, dba_tab_subpartitions b
        where a.table_name = '$ftable' and
                a.table_name = b.table_name (+) and
                a.partition_name=b.partition_name  (+)  and
                a.table_owner = b.table_owner (+) $AND_FOWNER
         order by a.partition_position,a.partition_name
/
EOF

# ...........................................................
# constraints
elif [ "$req" = "constraints" ];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 80 termout on heading off pause off embedded off verify off
select 'Date              -  '||to_char(sysdate,'Day Ddth Month YYYY  HH24:MI:SS'),
       'Username          -  '||USER  nline , 'List Constraints which point to the Table $ftable' nline from sys.dual
/
prompt
set embedded on heading on feedback off linesize 124 pagesize 66
col cn       form a24 head 'Foreign Keys:'
col col1     form a30 head 'Remote |Table.Colmuns'
col col0     form a22 head 'Table Columns'
col cond     form a40 head 'Col or Condition'
col typ      form a10 head 'Type of |Constraint'
col rc       form a22 head 'Remote|Constraint name'
col drule    form a6 head 'Delete|Rule'
col position form 999 head 'Pos'
col status   form a1  head 'S'
col st   form a10  head 'Status'
col owner new_value owner noprint
break on col1
$PROMPT
prompt .          S= Status   T= Contraint Type : R=Reference, C=check
prompt

select  a.constraint_name, decode(constraint_type,'U','UNIQUE','PRIMARY') typ,
            b.column_name cond, position, status st, a.DEFERRABLE,a.DEFERRED
     from dba_constraints a, dba_cons_columns b
           where a.table_name = upper('$ftable')
             and   constraint_type in ('U','P')
             and   a.constraint_name = b.constraint_name
             and   a.owner = b.owner
/
set head off
select a.constraint_name, 'Check' typ, search_condition cond,  1 position, status st,a.DEFERRABLE, a.DEFERRED
      from dba_constraints a, dba_cons_columns b
      where a.table_name =  '$ftable'
      and   constraint_type in ('C')
              and   a.constraint_name = b.constraint_name
              and   a.owner = b.owner
/

prompt
prompt
set head on
select  '(' || d.constraint_name || ')' cn, a.table_name||'.'||d.column_name col0,
         '->  '||b.table_name||'.'||c.column_name  col1, '('|| c.constraint_name|| ')' rc ,
     c.position,substr(a.status,1,1) status,
     decode(a.delete_rule,'CASCADE','ON CASCADE',' ') drule
     from all_cons_columns d,all_cons_columns c,all_constraints a , all_constraints b
     where
           b.constraint_name = a.r_constraint_name and
           d.owner = a.r_owner and
           a.table_name = '$ftable' and
           d.constraint_name = a.constraint_name and
           d.owner = a.owner and
           c.constraint_name = a.r_constraint_name and
           c.owner = a.r_owner and
           d.position = c.position
     order by d.position
/
prompt
prompt '-----------------------------'
prompt .  Who reference '$ftable' ?
prompt '-----------------------------'
prompt
col rc       form a61 head 'Remote|Constraint name'
col col0     form a61 head 'Local |Table.Colmuns'
select '['||a.owner||'].'||a.table_name||'.'||c.column_name||'  ('||a.constraint_name||')' rc,
'--> ['||a.r_owner||'].'||b.table_name||'.'||b.column_name ||'  ('||a.r_constraint_name||')' col0
 from all_constraints a , all_cons_columns b, all_cons_columns c
where b.table_name= '$ftable' and
      b.constraint_name = a.r_constraint_name  and
      c.constraint_name = a.constraint_name
/

prompt
exit
EOF


# ...........................................................
# extract DDL
# ...........................................................
elif [ "$req" = "ddl" ];then
sqlplus -s "$CONNECT_STRING"  <<EOF
select 'Date              -  '||to_char(sysdate,'Day Ddth Month YYYY  HH24:MI:SS'),
       'Username          -  '||USER  nline,
       'Create ddl for all subpartion of first partition in a partitioned table' nline from sys.dual
/
set head off
SET PAGESIZE 0
SET LONG 90000
set linesize 124
execute dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS_AS_ALTER', true );
execute dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', TRUE );
execute dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS', TRUE );
execute dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'REF_CONSTRAINTS', TRUE );
execute dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'TABLESPACE', true);
set serveroutput on size 99999 format wrapped
spool $FOUT.tmp
SELECT dbms_metadata.get_ddl('TABLE', '$ftable','$fowner') FROM dual
/
select dbms_metadata.get_ddl('INDEX',index_name,'$fowner') from (
select index_name from dba_indexes where table_name = '$ftable' and table_owner = '$fowner'
)
/
EOF
cat $FOUT.tmp |sed -e 's/ *$//' -e '/^ *$/d' > $FOUT ; rm $FOUT.tmp
echo "results in $FOUT"
exit
# ...........................................................
# Lobs 
# ...........................................................
elif [ "$req" = "lobs" ];then

if [ -n "$fowner" ];then
     AND_OWNER=" and us.owner = '$fowner' "
     AND_TOWNER=" and table_owner = '$fowner' "
     AND_I_OWNER=" and ui.owner='$fowner' and ui.owner = uip.index_owner "
     AND_IS_OWNER=" and ui.owner='$fowner' and ui.owner = uisp.index_owner "
fi

sqlplus -s "$CONNECT_STRING"  <<EOF

ttitle skip 2 'MACHINE $HOST - ORACLE_SID : $ORACLE_SID '  'Page:' format 999 sql.pno skip 2
column nline newline
set pagesize 66 linesize 120 
set 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, 'List tables info : $fowner' nline from sys.dual
/
column table_name format a20 heading "Table"
column owner format a15 heading "Owner"
column tablespace_name format a16 heading "Tablespace"
column column_name format a20 heading "column Name"
column segment_name format a25 heading "Segment Name"
column lob_column format a18 heading "Lob Column"
column bytes format 999990.99 head "Size(m)"
column LOB_PARTITION_NAME format a18 heading "Lob partition|name" justify c
col type format a14
set linesize 190 pagesize 66 feedback off termout on head on pause off

break on owner on segment_name on partition_name

comp sum of bytes on report


SELECT /*+ USE_NL(a) */
    a.owner,a.segment_name, a.partition_name,a.lob_column,
    a.lob_partition_name, a.tablespace_name,
    a.type,sum(bytes/1024/1024) bytes
 FROM (
          (SELECT TABLE_OWNER owner, 'partition_rows' type,table_name segment_name, partition_name, '-'lob_partition_name, tablespace_name,'-' lob_column
                      FROM dba_tab_partitions WHERE table_name = '$ftable' $AND_TOWNER)
          UNION
              (SELECT TABLE_OWNER owner, 'sub_partition' type,table_name segment_name, subpartition_name partition_name,
                       '-'lob_partition_name,tablespace_name,'-' lob_column
                       FROM dba_tab_subpartitions WHERE (table_name, partition_name) IN 
                      (SELECT table_name, partition_name FROM dba_tab_partitions WHERE  table_name = '$ftable' $AND_TOWNER) $AND_TOWNER)
          UNION
              (SELECT /*+ FIRST_ROWS */ TABLE_OWNER owner,'lob_partition' type, lob_name segment_name, partition_name,
                                         lob_partition_name,TABLESPACE_NAME, COLUMN_NAME lob_column
                     FROM dba_lob_partitions WHERE table_name = '$ftable' $AND_TOWNER)
          UNION
              (SELECT ui.owner,'idx_lob_part' type,uip.index_name segment_name, '-'partition_name,
                       uip.partition_name lob_partition_name ,uip.TABLESPACE_NAME,'-' lob_column
                       FROM  dba_indexes ui, dba_ind_partitions uip
                WHERE ui.table_name = '$ftable' $AND_I_OWNER
                  AND ui.index_type = 'LOB'
                  AND uip.index_name = ui.index_name)
          UNION
              (SELECT /*+ FIRST_ROWS */ TABLE_OWNER owner,'lob_subpart' type,lob_name segment_name,
                      lob_subpartition_name lob_partition_name,'-' partition_name,TABLESPACE_NAME,COLUMN_NAME lob_column
                 FROM dba_lob_subpartitions WHERE table_name = '$ftable' $AND_TOWNER)
          UNION
              (SELECT /*+ FIRST_ROWS */  ui.owner,'idx_lob_subpart' type, ui.index_name segment_name,
                       uisp.subpartition_name partition_name,'-'lob_partition_name, uisp.TABLESPACE_NAME,'-' lob_column
                 FROM dba_indexes ui, dba_ind_subpartitions uisp
                WHERE ui.table_name = '$ftable'
                  AND ui.index_type = 'LOB'
                  AND uisp.index_name = ui.index_name $AND_IS_OWNER)
      ) a,
      dba_segments us
  WHERE us.segment_name = a.segment_name $AND_OWNER
  AND (us.partition_name = a.partition_name or us.partition_name = a.lob_partition_name)
GROUP by  a.owner,a.type ,a.segment_name,a.partition_name,lob_partition_name,a.tablespace_name,lob_column
order by owner,partition_name
/

EOF

# ...........................................................
# chain rows
# ...........................................................
elif [ "$req" = "chain" ];then
  if [ -n "$FOWNER" ];then
      AND=" and"
   else
      unset AND
   fi

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 190 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, 'List tables with chained rows for Schema : $fowner' nline from sys.dual
/
$PROMPT
set pause off feed on verify on
column avg_row_len format 999990 heading "Average|Row len"
column pct_free format 990 heading "pct|Free"
column pct_used format 990 heading "pct|Used"
column pct format 990.99 heading "%|Chained" justify c
column chain_cnt  format 999,990 heading "Chained| Rows" justify c
column table_name format a30 heading "Table Name"
             set embedded on
             set linesize 80 pagesize 66
             set feedback on
             set termout on head on;
             select TABLE_NAME, NUM_ROWS, CHAIN_CNT,
                    decode(num_rows,0,0,(chain_cnt*100/num_rows)) pct,
                    pct_used,pct_free,avg_row_len
             from dba_tables
             where chain_cnt > 0 $AND $FOWNER
                   order by 100- (chain_cnt*100/num_rows)

/
EOF

# ...........................................................
else
if [ -z "$fowner"  ];then
    unset AND
elif [ -z "$ftable" ];then
    unset AND
fi

if [ "$ADDITIONAL_INFO" = "TRUE" ];then
             # avg row per block  = ( (avg free space in block/block_size)/100 ) * (max num of rows in a block)
            SQL_INFO1=" prompt
                      select ROW_MOVEMENT, EMPTY_BLOCKS, CHAIN_CNT, AVG_ROW_LEN,
                               round(nvl(floor(&fsize - 66 - INI_TRANS * 24)/greatest(AVG_ROW_LEN + 2, 11), 1),0) max_rpb,
                               (1-(avg_space/&fsize))* round(nvl(floor(&fsize - 66 - INI_TRANS * 24)/greatest(AVG_ROW_LEN + 2, 11), 1),0) avg_rpb,
                               num_rows/(trunc(nvl(floor(&fsize - 66 - INI_TRANS * 24)/greatest(AVG_ROW_LEN + 2, 11), 1),0))/blocks  dens,
                               trunc((1+FREELIST_GROUPS+BLOCKS)*&fsize) /1048576 HWM
                        from dba_tables where table_name = '$ftable' $AND_FOWNER ;"
            SQL_INFO2="prompt
                       SELECT AVG_SPACE_FREELIST_BLOCKS avg_sfp, INI_TRANS, MAX_TRANS, freelists, freelist_groups,
                        trim(DEGREE) deg, CACHE,  BUFFER_POOL, USER_STATS, IOT_TYPE, SAMPLE_SIZE
                        from dba_tables where table_name = '$ftable' $AND_FOWNER; "
fi


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 132 termout on pause off
set embedded on verify off heading off

select 'Date              -  '||to_char(sysdate,'Day Ddth Month YYYY  HH24:MI:SS'),
       'Username          -  '||USER  nline, 'Show partition table' nline
from sys.dual
/
col db_block_size new_value fsize noprint
set head off feed off
select value db_block_size from v\$parameter where name = 'db_block_size'
/
set head on
break on partition_position on partition_name on report
COL partition_position       FORMAT  9999 heading 'Part| Pos'
COL fsize                    FORMAT  9999999   heading 'Size (m)'
COL partition_name           FORMAT  A35 head 'Partition name'
COL subpartition_name        FORMAT  A25 head 'System generated|Subartition name'
COL table_name               FORMAT   A32 head 'Table name'
COL tablespace_name          FORMAT  A20   justify c HEAD 'Tablespace'
COL owner                    FORMAT  A16   justify c HEAD 'Owner'
COL glob                     FORMAT  A4   justify c HEAD 'Glob|stat'
col last_analyzed            format  A19  head 'Last Analyzed'
col created                  format  A19  head 'Created'
col last_ddl_time            format  A19  head 'Last ddl'
col temporary                format  A10  head 'Temporary'
comp sum of fsize on report
col row_movement head "Row|Movment" justify c
col avg_rpb format 9999999 head "Avg Row|Per blk" justify c
col max_rpb format 9999999 head "Max Row|Per blk" justify c
col empt_clocks head "Empty|Blocks"
col CHAIN_CNT head "Chain|Cnt" format 99999
col AVG_ROW_LEN head "Avg row|Len" format 9999999
col AVG_space format 999999 head "Avg|space"
col NUM_FREELIST_BLOCKS format 99999 head "Num|Frlist|block" justify c
col Empty_BLOCKS head "Empty|blocks"
col TEMPORARY head "Temp" format a5
col BUFFER_POOL format a8 head "Buffer|pool"
col GLOBAL_STATS head "Global|stats" format a5
col USER_STATS head "User|stats" format a5
col IOT_TYPE head "Iot" format a5
col SAMPLE_SIZE head "Sample| size" justify c
col deg head "Deg" format a3 truncate
col dens format 990.99 head "Data|Density" justify c
col ini_trans head "ini|tran" format 999
col max_trans head "max|tran" format 999
col avg_sfp head "Avg space|Free list block"  format 9999 justify c
col FREELISTS head "free|list"  format 999
col FREELIST_GROUPS head "Free list| Group"  format 999
col cache format a5
$PROMPT

$SQL_DDL
SELECT table_name, owner,trunc(NUM_ROWS)num_rows, blocks,BLOCKS * &fsize/1024/1024 fsize ,
       decode(tablespace_name,NULL, decode (IOT_TYPE,'IOT','- iot table -',
                       decode(PARTITIONED,'YES',' - partitioned -',decode(TEMPORARY,'Y','- Temp table -', ' --' ))),tablespace_name) tablespace_name,
       TO_CHAR(LAST_ANALYZED, 'MM/DD/YYYY HH24:MI:SS') last_analysed, global_stats glob
     FROM DBA_TABLES
     WHERE $FOWNER $AND $TABLE_LIKE
     order by 3 desc, TO_CHAR(LAST_ANALYZED, 'MM/DD/YYYY HH24:MI:SS') desc
/
$SQL_INFO1 
$SQL_INFO2 
prompt
EOF

fi