加载中…
个人资料
不会打鱼的渔民
不会打鱼的渔民
  • 博客等级:
  • 博客积分:0
  • 博客访问:123
  • 关注人气:0
  • 获赠金笔:0支
  • 赠出金笔:0支
  • 荣誉徽章:
相关博文
推荐博文
正文 字体大小:

shell脚本,创建oralce表空间,用户,授权

(2017-08-01 15:16:09)
标签:

shell

oracle

表空间

用户

授权

#!/bin/bash 
#create tablespace
if [ $# -lt 2 ]; then
  echo -e "\e[1;32m  ********************************************************************************* \e[0m" 
  echo -e "\e[1;31m  Usage: $0 USERNAME PASSWORD DEFAULT_TABLESPACE,username and passwd is not empty. \e[0m"
  echo -e "\e[1;32m  ********************************************************************************* \e[0m" 
  exit 1
fi

if [ $3 = ""] >/dev/null 2>&1
 then def_tbsp="USERS"
        chk_tbsp="USERS"
 else
     def_tbsp=$(echo $3 | tr '[a-z]' '[A-Z]')
     chk_tbsp=$(echo $3 | tr '[a-z]' '[A-Z]') 
 fi

ora_sys="sys"
ora_pass="oracle"
outfiletmp01=/tmp/createusertmp01.txt  #specify the output file location
outfiletmp02=/tmp/createusertmp02.txt  #specify the output file location
outfiletmp03=/tmp/createusertmp03.txt  #specify the output file location
outfiletmp04=/tmp/createusertmp04.txt  #specify the output file location
outfiletmp07=/tmp/createusertmp07.txt  #specify the output file location
outfiletmp08=/tmp/createusertmp08.txt  #specify the output file location

cre_user=$1
chk_user=$(echo $1 | tr '[a-z]' '[A-Z]')
cre_user_pwd=$2
#def_tbsp=$(echo $3 | tr '[a-z]' '[A-Z]')
#chk_tbsp=$(echo $3 | tr '[a-z]' '[A-Z]')
data_dir=$ORACLE_BASE/oradata


sqlplus -S " / as sysdba" <<!01 >/dev/null
set heading off;
set feedback off;
set termout off;
set pagesize 0;
set verify off;
set echo off;
spool ${outfiletmp01}
select tablespace_name from dba_data_files where tablespace_name='${chk_tbsp}';
spool off
exit;
!01

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

if [ $3 = ""] >/dev/null 2>&1
 then
#echo $cre_user
#echo jsjsjsjsjjs
sqlplus -S " / as sysdba" <<!00 >/dev/null
  set heading off;
 set feedback off;
 set termout off;
 set pagesize 0;
 set verify off;
 set echo off;
 spool ${outfiletmp07}
 select username from dba_users where username='${chk_user}'; 
 spool off
 exit;
!00
 
 tps_user=`grep -i ${chk_user} ${outfiletmp07}`
 
  if [ $tps_user = $chk_user ] >/dev/null 2>&1
    then
      echo -e "\e[1;31m  sorry, The username ${cre_user} is exits!  \e[0m"
   exit 0
    else
echo -e "\e[1;33m  user creating... \e[0m"
echo -e "\e[1;33m  granting... \e[0m"
  sqlplus -S " / as sysdba" <<!09 >/dev/null
  set heading off;
  set feedback off;
  set termout off;
  set pagesize 0;
  set verify off;
  set echo off;
  spool ${outfiletmp08}
  create user $cre_user identified by $cre_user_pwd;
  grant resource,connect to $cre_user;
  spool off
  exit;
!09
  fi
 
 #echo "user creating..."
 #echo "granting ..."
  ins_user=`grep -i "User created"  ${outfiletmp08} >${outfiletmp03} `
    if [ ! -s ${outfiletmp03} ]; then
   echo -e "\e[1;36m  ******************************************************************  \e[0m"
   echo -e "\e[1;31m  User created! Granted!!! \e[0m"
   echo -e "\e[1;36m  ******************************************************************  \e[0m"
 exit 0
 fi
fi


tps_jug=`grep -i ${chk_tbsp} ${outfiletmp01}`

if [ $tps_jug = $chk_tbsp ]  >/dev/null 2>&1
  then
    echo -e "\e[1;31m  sorry, The tablespacename ${def_tbsp} is exits!  \e[0m"
 exit 
  else
echo -e "\e[1;33m  tablespace creating... \e[0m"
   sqlplus -S " / as sysdba" <<!02 >/dev/null
set heading off;
set feedback off;
set termout off;
set pagesize 0;
set verify off;
set echo off;
spool ${outfiletmp02}
create tablespace $def_tbsp datafile '$data_dir/$def_tbsp.dbf' size 1g autoextend on;
spool off
exit;
!02
fi
#echo "tablespace creating..."
ins_tbsp=`grep -i "created"  ${outfiletmp02} > ${outfiletmp03} `
 if [ ! -s ${outfiletmp03} ]
  then
 echo -e "\e[1;31m  ******************************************************************  \e[0m"
 echo -e "\e[1;31m  Tablespace created!  \e[0m"
 echo -e "\e[1;31m  ******************************************************************  \e[0m"
fi


sqlplus -S " / as sysdba" <<!03 >/dev/null
 set heading off;
set feedback off;
set termout off;
set pagesize 0;
set verify off;
set echo off;
spool ${outfiletmp04}
select username from dba_users where username='${chk_user}'; 
spool off
exit;
!03

tps_user=`grep -i ${chk_user} ${outfiletmp04}`
 
 if [ "${tps_user}" = "${chk_user}" ]
   then
     echo -e "\e[1;31m  sorry, The username ${cre_user} is exits!  \e[0m"
  exit 0
   else
echo -e "\e[1;33m  user creating... \e[0m"
echo -e "\e[1;33m  grant... \e[0m"
    sqlplus -S " / as sysdba" <<!04 >/dev/null
 set heading off;
 set feedback off;
 set termout off;
 set pagesize 0;
 set verify off;
 set echo off;
 spool ${outfiletmp04}
 create user $cre_user identified by $cre_user_pwd default tablespace $def_tbsp;
 grant resource,connect to $cre_user;
 spool off
 exit;
!04
 fi

#echo "user creating..."
#echo "granting ..."
 ins_user=`grep -i "User created"  ${outfiletmp04} >${outfiletmp03} `
   if [ ! -s ${outfiletmp03} ]; then
  echo -e "\e[1;31m  ******************************************************************  \e[0m"
  echo -e "\e[1;31m  User created! Granted!!! \e[0m"
  echo -e "\e[1;31m  ******************************************************************  \e[0m"
exit 0
fi

0

阅读 评论 收藏 转载 喜欢 打印举报/Report
前一篇:windows下配置DG
  • 评论加载中,请稍候...
发评论

    发评论

    以上网友发言只代表其个人观点,不代表新浪网的观点或立场。

    < 前一篇windows下配置DG
      

    新浪BLOG意见反馈留言板 电话:4000520066 提示音后按1键(按当地市话标准计费) 欢迎批评指正

    新浪简介 | About Sina | 广告服务 | 联系我们 | 招聘信息 | 网站律师 | SINA English | 会员注册 | 产品答疑

    新浪公司 版权所有