#! /bin/sh
###############################################
# function getSessionlist: #
# get the session of the user that connect DB #
###############################################
function getSessionlist {
username=$1
date "+%m%d-%H%M%S" >execsql.log
sqlplus / as sysdba >>execsql.log << EOF
set lines 100;
set trims on;
set heading off;
spool /opt/oracle/admin/mdspdb/dpdump/session.lst
select t.username,t.sid,t.serial# from v\$session t where t.username=upper('${username}');
spool off;
exit;
EOF
#*****clear up unuseful lines, delete first 2 lines & last 2 lines***
sed -i '1,2d;N;$!P;$!D;$d' session.lst
}
#####################################
# function cleanup: #
#if session file & logfile,clean up #
#####################################
function cleanup {
if [ -f session.lst ]
then
:> session.lst
echo "cleanup session.lst successful!"
fi
if [ -f execsql.log ]
then
:> execsql.log
echo "clearup execsql.log successful!"
fi
}
##########################################################
# function recreateUser1: #
#if the user has not connect session, using recreateUser1#
##########################################################
function recreateUser1 {
username=$1
password=$2
##kill the active session
echo "Begin import the db data,please wait 5-10 minutes......"
sqlplus / as sysdba >>execsql.log << EOF
drop user ${username} cascade;
create user ${username} identified by ${password} default tablespace CBS_DEFAULT_DAT temporary tablespace TEMP;
grant dba to ${username};
grant unlimited tablespace to ${username};
commit;
exit;
EOF
}
##########################################################
# function recreateUser2: #
#if the user has connect session, using recreateUser2 #
##########################################################
function recreateUser2 {
username=$1
password=$2
##kill the active session
echo "Begin import the db data,please wait 5-10 minutes......"
sid=`awk -F' ' '{print $2","$3}' session.lst`
for vsid in ${sid}
do
sqlplus / as sysdba >>execsql.log << EOF
alter system kill session '${vsid}';
exit;
EOF
done
sqlplus / as sysdba >>execsql.log << EOF
drop user ${username} cascade;
create user ${username} identified by ${password} default tablespace CBS_DEFAULT_DAT temporary tablespace TEMP;
grant dba to ${username};
grant unlimited tablespace to ${username};
commit;
exit;
EOF
}
#*************************** excute part *****************************
read -p "Please input oracle_username that you want kill:" oracle_username
read -p "Please input ${oracle_username}'s password:" user_password
cleanup
getSessionlist ${oracle_username}
if [ "`cat session.lst`" = "no rows selected" ]
then
recreateUser1 ${oracle_username} ${user_password}
else
recreateUser2 ${oracle_username} ${user_password}
fi
#********************* import db by Oracle dump ****************************
impdp ${oracle_username}/${user_password}@mdspdb directory=DATA_PUMP_DIR dumpfile=bfmdbexpdb.dmp TRANSFORM=OID:N REMAP_SCHEMA=bfmdb1:${oracle_username} logfile=`date "+%m%d-%H%M%S"`imbf.log