<<INFO
AUTHOR:运维@小兵
DATE:2021-09-13
DESCRIBE:如创建/删除用户、表空间、授权
SYSTEM:CentOS7/RedHat7
WARNING:
MODIFY:
INFO
set -e
source /etc/profile
DB_IP=$1
DB_PORT=$2
ORACLE_SVC_NAME=$3
DB_ADMIN_USER=$4
DB_ADMIN_PWD=$5
SQL_CMD="sqlplus ${DB_ADMIN_USER}/${DB_ADMIN_PWD}@${DB_IP}:${DB_PORT}/${ORACLE_SVC_NAME}"
Check_Env(){
echo "INFO:Begin Check Env..."
! command -v sqlplus && echo "ERROR:sqlplus Command Not Exist" && exit 1
if ! echo "select status from v\$instance;" | ${SQL_CMD} | grep -w '^OPEN$';then
echo "ERROR:${DB_IP} Database Access Fail" && exit 1
fi
}
#创建用户和表空间
Create_User_Tablespace(){
[[ $# -eq 0 ]] && echo "ERROR:User Info Is Null,Please Excute:bash $0 -h" && exit 1
local dba_data_file_path=$(echo "select file_name from dba_data_files;" | ${SQL_CMD} | grep -w 'system01.dbf')
dba_data_file_path=$(echo ${dba_data_file_path%/*}) #获取表空间文件的位置
[[ -z ${dba_data_file_path} ]] && echo "ERROR:dba_data_file_path Is Null" && exit 1
for info in $@
do
local info_array=(${info//:/ })
if [[ ${#info_array[*]} -ne 3 ]];then
echo "ERROR:${info} Format Error,Please Excute:bash $0 -h" && exit 1
fi
#local db_user=$(echo ${info_array[0]} | tr 'a-z' 'A-Z') #把用户名转换为大写
local db_user=${info_array[0]}
local db_pwd=${info_array[1]} #数据库用户密码
local db_tablespace=${info_array[2]} #表空间名
echo "INFO:Begin Create database User ${db_user} And Tablespace ${db_tablespace}..."
#创建表空间
if ! echo "select * from Dba_Tablespaces;" | ${SQL_CMD} | egrep -iw "^${db_tablespace}\b" &> /dev/null;then
echo "create tablespace ${db_tablespace} datafile '${dba_data_file_path}/${db_user}01.dbf' size 200m autoextend on;" | ${SQL_CMD}
if echo "${db_user}" | grep "^db_test_" &> /dev/null;then
echo "ALTER TABLESPACE ${db_tablespace} ADD DATAFILE '${dba_data_file_path}/${db_user}02.dbf' size 200m autoextend on;" | ${SQL_CMD}
fi
if echo "select * from Dba_Tablespaces;" | ${SQL_CMD} | egrep -iw "^${db_tablespace}\b" &> /dev/null;then
echo "INFO:Create Tablespace ${db_tablespace} Success"
else
echo "INFO:Create Tablespace ${db_tablespace} Fail" && exit 1
fi
else
echo "INFO:tablespace ${db_tablespace} Is Exist"
fi
#创建用户
if ! echo "select username from dba_users;" | ${SQL_CMD} | grep -iw "^${db_user}$" &> /dev/null;then
${SQL_CMD} << EOF
create user ${db_user} identified by ${db_pwd} default tablespace ${db_tablespace};
grant connect, resource,IMP_FULL_DATABASE to ${db_user};
grant read,write on directory DATA_PUMP_DIR to ${db_user};
ALTER USER ${db_user} QUOTA UNLIMITED ON ${db_tablespace};
EOF
if echo "select username from dba_users;" | ${SQL_CMD} | grep -iw "^${db_user}$" &> /dev/null;then
echo "INFO:Create db_user ${db_user} Success"
else
echo "ERROR:Create db_user ${db_user} Fail" && exit 1
fi
else
echo "INFO:db_user ${db_user} Is Exist"
fi
done
}
#删除用户
Drop_User(){
local db_user=""
for db_user in $@
do
db_user=$(echo ${db_user} | tr 'a-z' 'A-Z') #把用户名转换为大写
#将sid和serial保存到一个文件中
#echo "select sid,serial# from v\$session where username='${db_user}';" | ${SQL_CMD} | egrep '^ +[0-9]+' | awk '{printf"%s %s\n",$1,$2}' > /tmp/${db_user}_sid.txt
if echo "select username from dba_users;" | ${SQL_CMD} | grep -iw "^${db_user}$" &> /dev/null;then
local user_id=$(echo "select user_id from dba_users where username='${db_user}';" | ${SQL_CMD} | grep -A2 -w 'USER_ID' | tail -n+3 | awk '{print $1}')
[[ -n ${user_id} ]] && echo "delete obj\$ where owner#='${user_id}'" | ${SQL_CMD}
echo "INFO:Begin Drop User ${db_user}..."
echo "drop user ${db_user} cascade;" | ${SQL_CMD}
if ! echo "select username from dba_users;" | ${SQL_CMD} | grep -iw "^${db_user}$" &> /dev/null;then
echo "INFO:Drop User ${db_user} Success"
else
echo "ERROR:Drop User ${db_user} Fail" && exit 1
fi
else
echo "INFO:db_user ${db_user} Not Is Exist"
fi
done
}
#删除表空间
Drop_Tablespace(){
local db_tablespace=""
for db_tablespace in $@
do
db_tablespace=$(echo ${db_tablespace} | tr 'a-z' 'A-Z') #把表空间名转换为大写
if echo "select * from Dba_Tablespaces;" | ${SQL_CMD} | egrep -iw "^${db_tablespace}\b" &> /dev/null;then
echo "INFO:Begin Drop Tablespace ${db_tablespace}..."
echo "drop tablespace ${db_tablespace} including contents and datafiles CASCADE CONSTRAINTS;" | ${SQL_CMD}
if ! echo "select * from Dba_Tablespaces;" | ${SQL_CMD} | egrep -iw "^${db_tablespace}\b" &> /dev/null;then
echo "INFO:Drop Tablespace ${db_tablespace} Success"
else
echo "ERROR:Drop Tablespace ${db_tablespace} Fail"
fi
else
echo "INFO:tablespace ${db_tablespace} Not Is Exist"
fi
done
}
#帮助文档
Help(){
cat << EOF
Usage:
bash $0 IP 端口 oracle服务名 管理员用户名 密码 -c
WARNING:
当传入的位置参数使用冒号(:)分隔时,冒号两边的值不能包含冒号(:)
=======================================================================
optional arguments:
-h 提供帮助信息
-c 进行创建的相关操作
user 创建数据库用户和表空间,格式:-c user 用户1:密码1:表空间1 用户2:密码2:表空间2,如-c user db_test_test:db_test_test:db_test_test console_test:console_test:console_test
-d 进行删除的相关操作
user 删除用户,多个用户使用空格间隔,如:-d 用户1 用户2
ts 删除表空间,多个表空间使用空格间隔,如:-d 表空间1 表空间2
EXAMPLE:
bash $0 192.168.1.2 1521 orcl system 123456 -c user db_test:db_test:db_test
EOF
}
######################主程序######################
[[ "x$1" == "x-h" ]] && Help && exit 0
[[ $# -le 6 ]] && echo "ERROR:Invalid Param!!!,Please Excute:bash $0 -h" && exit 1
Check_Env
if [[ "x$6" == "x-c" ]];then
shift 6
case $1 in
user)
shift 1
Create_User_Tablespace $@;;
*)
echo "ERROR:Invalid Param!!!,Please Excute:bash $0 -h" && exit 1
esac
elif [[ "x$6" == "x-d" ]];then
shift 6
case $1 in
user)
shift 1
Drop_User $@;;
ts)
shift 1
Drop_Tablespace $@;;
esac
else
echo "Help" && exit 1
fi