#!/usr/bin/ksh
#whatrowcount ()
#------------------------------------------------------------------------------#
#    Module Name:  whatrowcount                                                #
#                                                                              #
#    Purpose:      Report on the Schema-Name Table-Row-Counts.                 #
#                                                                              #
#    Maintenance                                                               #
#    Date          Author        Description                                   #
#    -----------   ------------  --------------------------------------------  #
#    05-Feb-2008   M.Barone      Module design/creation                        #
#------------------------------------------------------------------------------#
{
        DIRNAM=$(dirname $0)
        ORASID=$(echo $CONTEXT_NAME | cut -f1 -d'_')
        HNAME=$(uname -n)

        #----------------------------------------------------------------------#
        #  TEST:  Number ($#) of arguments passed to this function?            #
        #----------------------------------------------------------------------#
        if      [ $# = 0 ]
        then
            clear
            echo 
            echo  " #--------------------------------------------------------#"
            echo  " # whatrowcount:                   Oracle Table-Row-Count #"
            echo  " #--------------------------------------------------------#"
            echo                                                               

            #------------------------------------------------------------------#
            #  Trap:  Set CNTL-C CNTL-D CNTL-\ Before Password Protection      #
            #------------------------------------------------------------------#
            trap        "stty echo; return"     2                              
            trap        "stty echo; return"     3
            stty -echo

            read PASSWD?" Please Enter the Oracle ^[[7mSYSTEM^]]0m Passwd:    "

            stty echo
            echo 
            #------------------------------------------------------------------#
            #  Trap:  Reset CNTL-C CNTL-D CNTL-\ After Password Protection     #
            #------------------------------------------------------------------#
            trap        2
            trap        3

            read USRNAM?"        Enter Schema-Name ^[[7mGL or BOM etc.^[[0m : "
            clear
        else
            PASSWD=$1
            USRNAM=$2
            clear
        fi


#------------------------------------------------------------------------------#
#  Initialize                                                                  #
#------------------------------------------------------------------------------#
        SPOOLNM=/tmp/whatrowcount_$$.txt

#------------------------------------------------------------------------------#
#  SQL*Plus ("Here-Document")                                                  #
#------------------------------------------------------------------------------#
sqlplus  -s  /nolog  <<-END_FILE

        CONNECT  SYSTEM/$PASSWD

        clear   BREAKS
        clear   BUFFER
        clear   COLUMNS
        clear   COMPUTES
        clear   SCREEN
        clear   SQL
        clear   TIMING

        set     feedback        off
        set     flush           on
        set     heading         off     
        set     pause           off
        set     space           1
        set     termout         on
        set     verify          on
        set     linesize        80
        set     pagesize        9000

        SPOOL /tmp/whatrowcount_$$.sql

        COL     LINE1   format A75
        COL     LINE2   format A75
        COL     LINE3   format A75
        COL     LINE4   format A75
        COL     LINE5   format A75
        COL     LINE6   format A75
        COL     LINE7   format A75

        SELECT  'set   termout  on  verify on  heading off'             LINE1,
                'set   feedback off'                                    LINE2,
                'set   pagesize 9000   linesize 80'                     LINE3,
                'COL   TABNAM   FORMAT A48'                             LINE4,
                'COL   CNTNAM   FORMAT 99999999'                        LINE5,
                'SPOOL ${SPOOLNM}'                                      LINE6
        FROM    DUAL;


        SELECT  'SELECT '    || '''' ||
                '      ${ORASID} (${HNAME}) ${USRNAM}.Tables RowCount' ||
                ''''         || ','                                     LINE1,
                ''''         || '      '  ||  ''''  || ','              LINE2,
                ''''         ||
                '-------------------------------------------------' ||
                ' --------'  || ''''                                    LINE3,
                'FROM DUAL;'                                            LINE4
        FROM    DUAL;


        SELECT  'SELECT '    || '''' ||
                 TBL.owner   || '.'  || TBL.table_name || '''' ||
                 '  TABNAM'  || ','                                     LINE1,
                 'count(*) CNTNAM  from ' ||
                TBL.owner    || '.'  || TBL.table_name || ';'           LINE2
        FROM    ALL_TABLES   TBL
        WHERE   TBL.owner = '${USRNAM}'
        ORDER BY owner, table_name;

        @/tmp/whatrowcount_$$.sql
EXIT;
END_FILE

print
print "Please see /tmp/whatrowcount_$$.txt"
print
}
