Oracle 建立使用者shell指令碼執行個體

來源:互聯網
上載者:User

Oracle 建立使用者shell指令碼執行個體

Oracle 建立使用者shell指令碼執行個體代碼:

#!/bin/bash
#ocpyang@126.com
#create user


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


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


ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1


ORACLE_SID=orcl


ora_data=/u01/app/oracle/product/11.2.0/db_1/dbs/

 


ora_user="sys"


ora_pass="password"

 


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


cre_user=$1
chk_user=$(echo $1 | tr '[a-z]' '[A-Z]')
cre_user_pwd=$2
def_tbsp=$3
def_idx_tbsp=$4
def_temp_tbsp=temp
smk_test_table=t1

 


sqlplus -S "${ora_user}/${ora_pass} as sysdba" <<!01 >/dev/null #禁止sqlplus執行結果回顯
set heading off;
set feedback off;
set termout off;
set pagesize 0;
set verify 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 -i ${chk_user} ${outfiletmp01} `

 

 


if [ "${tps_jug}" = "${chk_user}" ]; then
 echo -e "\e[1;31m  sorry, The username ${cre_user} exits!  \e[0m"
 exit 0


else
 wind_crtusr=$(
 sqlplus -S "${ora_user}/${ora_pass} as sysdba" <<!01 >/dev/null #禁止sqlplus執行結果回顯
 spool ${outfiletmp02}
 CREATE USER ${cre_user} IDENTIFIED BY ${cre_user_pwd}
 DEFAULT TABLESPACE ${def_tbsp}
 TEMPORARY TABLESPACE ${def_temp_tbsp};


 GRANT CREATE SESSION TO ${cre_user};
 GRANT CREATE TABLE  TO ${cre_user};
 GRANT CREATE INDEX  TO ${cre_user};


 ALTER USER ${cre_user} QUOTA UNLIMITED ON ${def_tbsp};
 ALTER USER ${cre_user} QUOTA UNLIMITED ON ${def_idx_tbsp};


 -- Smoke test
 CONN ${cre_user}/${cre_user_pwd}
 CREATE TABLE ${smk_test_table}(tid NUMBER) TABLESPACE ${def_tbsp};
 CREATE INDEX ${smk_test_table}.idx1 ON ${cre_user}.${smk_test_table}(tid) TABLESPACE ${def_idx_tbsp};
 INSERT INTO ${smk_test_table} VALUES(1);
 DROP TABLE ${smk_ttbl};
 spool off
 exit;
 !01)
 


 # execute the variable $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}
 rm -rf ${outfiletmp03}
 exit 1
 
fi

Oracle 11g 在RedHat Linux 5.8_x64平台的安裝手冊

Linux-6-64下安裝Oracle 12C筆記

在CentOS 6.4下安裝Oracle 11gR2(x64)

Oracle 11gR2 在VMWare虛擬機器中安裝步驟

Debian 下 安裝 Oracle 11g XE R2

相關文章

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.