實戰:oracle新建用戶shell腳本

實戰:oracle新建用戶shell腳本

#!/bin/bash
#[email protected]
#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