删除oracle数据库用户

  手工删除ORACLE数据库用户时常会出现会话进程仍在使用导致删除失败的情况。需要查询会话并将会话删除才能成功将数据库用户删除,比较不方便。适用场景  自动删除oracle数据库用户脚本代码  脚本名称:drop_user.sh
#!/bin/bash

function 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,exitn";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} successn";exit;} || {printf "Drop the user of ${DBNAME} failn";exit 1;}
 

相关内容推荐