#!/bin/ksh
#
# Author: Mohit Dubey
# Visit http://www.geocities.com/md_seraphin for more goodies!
#
# This program is distributed under the GNU Public License Version 2
# with the additional privisio that the original author's name and
# contact details must be retained as-is in any modified or copied
# versions of this program.
#
printf "\nSchema login [uname/pass@db]: "
read ORA_LOGIN foo
printf "\n"

if [[ -z $ORA_LOGIN ]]
then
   printf "No credentials supplied!\n\n"
   exit
fi

printf "Validating credentials..."
if $(sqlplus -s $ORA_LOGIN <<-Eosql | /usr/xpg4/bin/grep -q ORA-
			exit;
		Eosql)
then
   printf "invalid credentials!\n\n"
   exit;
fi
printf "done\n"

INPIPE=/tmp/inPipe.$$
OUTPIPE=/tmp/outPipe.$$

/bin/rm -f $INPIPE && mknod $INPIPE p
/bin/rm -f $OUTPIPE && mknod $OUTPIPE p

sqlplus -s $ORA_LOGIN <$INPIPE 1>$OUTPIPE 2>&1 &
#jobs -l %1
printf "Spawned analyzer with PID %s\n" $(jobs -l %1 | nawk '{printf "%s",$3;}')

cat $OUTPIPE | sed '/^$/d' &
#jobs -l %2
printf "Spawned reader with PID %s\n" $(jobs -l %2 | nawk '{printf "%s",$3;}')
print "Analyzing schema tables (this may take a while)...\n"

for table_name in $(sqlplus -s $ORA_LOGIN <<-EoSQL | nawk '{printf "%s ",$1;}'
set head off pau off feedback off timing off trimspool on pages 0 lines 132
select table_name from user_tables;
exit;
EoSQL)
do
	print "set head off pau off feedback off timing off trimspool on pages 0 lines 132" \
		 >$INPIPE
	print "select decode(count(1),0,lpad('${table_name}',30,' ')||' has 0 rows',NULL) from $table_name;" \
		 >$INPIPE
done 

wait
printf "\nAnalysis Completed\n\n"

/bin/rm -f $INPIPE $OUTPIPE