Linux环境下 shell脚本创建oracle用户

注: 1)       脚本内有grant common_role to username;

                common_role 是需要提前报建的角色,该角色的权限看自己需要定义

      2)     该脚本需要在oracle用户下执行

      3)     $() 不是所有shell都支持的,需要用 ``来代替

      4)     红色标注为需要根据自己的实际情况修改的oracel信息

 shell脚本内容如下:

#!/bin/bash
# create user

if [ $# -ne 2 ]; then
   echo -e "\e[1;32m **************************** \e[0m"
   echo -e "\e[1;32m Usage: $0 USERNAME PASSWORD  \e[0m"
   echo -e "\e[1;32m **************************** \e[0m"
   exit 1
fi

# configure oracle env:about oracle envs ,username and password

ORACLE_HOME=/home/oracledb/11g

ORACLE_SID=orcl

# the dbf files's directory
ora_data=/home/oracledb/oradata/orcl/

data_filepath="${ora_data}${1}.dbf"

ora_user="system"

ora_pass="mypassword"

outfiletmp01=/tmp/createusertmp01.txt

outfiletmp02=/tmp/createusertmp02.txt

outfiletmp03=/tmp/createusertmp03.txt

cre_user=$1
#change username from lowercase to uppercase
chk_user=$( echo $1 | tr '[a-z]' '[A-Z]')
cre_user_pwd=$2
def_tbsp="TBS_${chk_user}"

sqlplus  "${ora_user}/${ora_pass} as sysdba" <<!01 >/dev/null
set heading off;
set feedback off;
set termout off;
set pagesize 0;
set verity off;
set echo off;
spool ${outfiletmp01};

select username from dba_users where username='${chk_user}';
spool off;
exit;
!01

## check oracle instance is down or up
ins_jug=`grep -i "ORA-01034:"  ${outfiletmp01} >${outfiletmp03} `
if [ -s ${outfiletmp03} ]; then
    echo -e "\e[1;31m  ********************************************  \e[0m"
    echo -e "\e[1;31m  !!!!, oracle IS down!  \e[0m"
    echo -e "\e[1;31m  ********************************************  \e[0m"
    exit 0
fi

tps_jug=` grep -c ${chk_user} ${outfiletmp01} `
count=1
if [ $tps_jug -gt $count ]; then 
   echo -e "\e[1;31m  sorry, The username ${cre_user} exits!  \e[0m"
   exit 0
else
   wind_crtusr=`
   sqlplus  "${ora_user}/${ora_pass} as sysdba" <<!01 >/dev/null
   spool ${outfiletmp02}
   create tablespace ${def_tbsp} logging datafile '${data_filepath}' size 500m autoextend on next 10m maxsize 30g;
   create user ${cre_user} identified by ${cre_user_pwd} default tablespace ${def_tbsp};
   grant common_role to ${cre_user};
   alter user ${cre_user} quota unlimited on ${def_tbsp};
   spool off;
   exit;
   `

   # execute the variable $wind_crtusr
   echo ${wind_crtusr}
   if ["" = ${wind_crtusr}]; then
       echo -e "\e[1;32m  ok, The create user ${cre_user} success!  \e[0m"
   else
       echo -e "\e[1;31m  ${wind_crtusr}   \e[0m"
   fi

   rm -rf ${outfiletmp01}
   rm -rf ${outfiletmp02}
fi