#!/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.
#

drop_user_objs()
{
	SQLLOGIN=$1
	LOGPATH=$2
	SQLFILE=${LOGPATH}/drop_${SQLLOGIN%%/*}.sql
	SQLLOG=${LOGPATH}/drop_${SQLLOGIN%%/*}.log

	printf "   generating drop script...\n"
	sqlplus -s ${SQLLOGIN} <<-EoSQL 1>${SQLFILE} 2>&1	
		set head off pau off feedback off timing off trimspool on pages 0 lines 132
		select 'drop '||a.object_type||' '||
		       decode(a.object_type,'DATABASE LINK','',user||'.')||
		       a.object_name||';'
		from   user_objects a, dual b
		where  object_type not in ('PACKAGE BODY','INDEX','TRIGGER')
		order  by object_type, object_name;
		exit;
	EoSQL
	printf "exit;\n" >> ${SQLFILE}

	printf "   dropping database objects (This may take a while)...\n"
	numObjsToDrop=$(grep -c '^drop ' ${SQLFILE})
	sqlplus -s ${SQLLOGIN} @${SQLFILE} 1>${SQLLOG} 2>&1
	numObjsDropped=$(grep -c ' dropped' ${SQLLOG})
	printf "   (${numObjsDropped}) of (${numObjsToDrop}) objects dropped\n"
	/bin/rm -f ${SQLFILE}
}

trap "print; print Terminating...; print; exit;" ERR

typeset -u usrResp

# Modify the ZIP command below appropriately
ZIP="gzip -9"

export ORACLE_HOME=/sbcimp/run/tp/oracle/client/v8.1.7
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH

DMPPATH=${HOME}/oracp_data/dmp
LOGPATH=${HOME}/oracp_data/log

if [[ ! -x ${DMPPATH} ]]
then
	if [[ ! -x ${DMPPATH%/*} ]]
	then
		/bin/mkdir ${DMPPATH%/*}
	fi
	/bin/mkdir ${DMPPATH}
fi

if [[ ! -x ${LOGPATH} ]]
then
	/bin/mkdir ${LOGPATH}
fi

printf "\n        Source schema to be copied [uname/pass@db]: "
read srcLogin foo

printf "Destination schema to be refreshed [uname/pass@db]: "
read dstLogin foo

if [[ -z ${srcLogin} || -z ${dstLogin} ]]
then
   printf "\nNo src/dest credentials supplied!\n\n"
   exit
fi

printf "\nValidating credentials..."
if $(sqlplus -s ${srcLogin} <<-Eosql | /usr/xpg4/bin/grep -q ORA-
			exit;
Eosql
)
then
   printf "src invalid!\n\n"
   exit;
fi
printf "src verified, "

if $(sqlplus -s ${dstLogin} <<-Eosql | /usr/xpg4/bin/grep -q ORA-
			exit;
Eosql
)
then
   printf "dest invalid!\n\n"
   exit;
fi
printf "dest verified.\n"

srcSchema=${srcLogin%%/*}
dstSchema=${dstLogin%%/*}
srcExpDmp=${DMPPATH}/exp_${srcSchema}.dmp
srcExpLog=${LOGPATH}/exp_${srcSchema}.log
dstImpLog=${LOGPATH}/imp_${dstSchema}.log

printf "\nWARNING: %s@%s will be COMPLETELY overwritten. All data will\n" ${dstSchema} ${dstLogin##*@}
printf "         be lost and all tables, packages etc. deleted.\n"
printf "         THIS IS AN UNRECOVERABLE ACTION!\n\n"
printf "Do you wish to continue? [y/n]: "
read usrResp foo

if [[ ${usrResp} != "Y" ]]
then
	printf "\n%s terminated on user request\n\n" ${0##*/}
	exit
fi

printf "\nExporting %s@%s...\n" ${srcSchema} ${srcLogin##*@}
exp userid=${srcLogin} \
    compress=n         \
    buffer=102400      \
    file=${srcExpDmp}  \
    log=${srcExpLog}   \
    grants=n           \
1>/dev/null 2>&1

if /usr/xpg4/bin/grep -q ORA- ${srcExpLog}
then
	printf "   errors detected! Check log [%s]\n\n" ${srcExpLog}
	exit
else
	printf "   done to [%s]\n" ${srcExpDmp}
fi

printf "Cleaning %s@%s...\n" ${dstSchema} ${dstLogin##*@}
drop_user_objs ${dstLogin} ${LOGPATH}

printf "Importing %s to %s@%s (This may take a while)...\n" ${srcExpDmp##*/} ${dstSchema} ${dstLogin##*@}
imp userid=${dstLogin} \
    commit=y           \
    buffer=102400      \
    fromuser=${srcSchema}   \
    touser=${dstSchema}     \
    file=${srcExpDmp}  \
    log=${dstImpLog}   \
    grants=n           \
1>/dev/null 2>&1

if /usr/xpg4/bin/grep -q ORA- ${dstImpLog}
then
	printf "   errors detected! Check log [%s]\n\n" ${dstImpLog}
	exit
else
	printf "   done\n"
fi

printf "Compressing extract file...\n"
${ZIP} ${srcExpDmp}
printf "   done\n"

printf "\n%s@%s successfully refreshed with %s@%s\n\n" ${dstSchema} ${dstLogin##*@} ${srcSchema} ${srcLogin##*@}