刪除oracle資料庫使用者

標籤：gre   成功   users   class   turn   $0   dev   exec   size   

  手工刪除ORACLE資料庫使用者時常會出現會話進程仍在使用導致刪除失敗的情況。需要查詢會話並將會話刪除才能成功將資料庫使用者刪除，比較不方便。

適用情境

  自動刪除oracle資料庫使用者

指令碼代碼

  指令碼名稱：drop_user.sh

#!/bin/bashfunction isExist_dbuser(){  [ $# -ne 1 ] && { printf "Call the function isExist_dbuser error.\n";return 1;}  local dbname=$(echo $1|tr ‘[a-z]‘ ‘[A-Z]‘)  local SELECT_DBNAME_SQL="select_dbname.sql"  local SELECT_DBNAME_RESULT="select_dbname.result"      rm -f "${SELECT_DBNAME_SQL}"  touch "${SELECT_DBNAME_SQL}"  echo "select username from dba_users;" >> "${SELECT_DBNAME_SQL}"  echo "exit" >> "${SELECT_DBNAME_SQL}"  sqlplus -S / as sysdba < "${SELECT_DBNAME_SQL}" > "${SELECT_DBNAME_RESULT}"  grep "^${dbname}$" "${SELECT_DBNAME_RESULT}" &> /dev/null && return 0 || return 1}[ $# -ne 1 ] && { printf "Usage:$(basename $0) dbname";exit 1;}[ $(whoami) != ‘oracle‘ ]&&{ printf "Please execute script on the oracle user,exit\n";exit 1;}DBNAME=$(echo $1|tr ‘[a-z]‘ ‘[A-Z]‘)KILL_SESSION_SQL="kill_session.sql"KILL_SESSION_RESULT="kill_session.result"DROP_USER_SQL="drop_user.sql"rm "${KILL_SESSION_SQL}" "${KILL_SESSION_RESULT}" "${DROP_USER_SQL}"touch "${KILL_SESSION_SQL}" "${KILL_SESSION_RESULT}" "${DROP_USER_SQL}"isExist_dbuser ${DBNAME} || {printf "The user of ${DBNAME} not exist,please input again.\n";exit 1;}echo "SELECT ‘alter system kill session ‘||‘‘‘‘ ||t.sid ||‘,‘||t.SERIAL#|| ‘‘‘‘ FROM v$session t WHERE t.USERNAME="${DBNAME}";" >> "${KILL_SESSION_SQL}"sqlplus -S / as sysdba < "${KILL_SESSION_SQL}" > "${KILL_SESSION_RESULT}"grep ‘^alter system kill session‘ "${KILL_SESSION_RESULT}" >> "${DROP_USER_SQL}"echo "drop user ${dbaname} cascade;" >> "${DROP_USER_SQL}"echo "exit" >> "${DROP_USER_SQL}"sqlplus -S / as sysdba < "${DROP_USER_SQL}"isExist_dbuser ${DBNAME} && {printf "Drop the user of ${DBNAME} success\n";exit;} || {printf "Drop the user of ${DBNAME} fail\n";exit 1;}

 

刪除oracle資料庫使用者