实战: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
本站文章为和通数据库网友分享或者投稿,欢迎任何形式的转载,但请务必注明出处.
同时文章内容如有侵犯了您的权益,请联系QQ:970679559,我们会在尽快处理。