导出数据shell脚本

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