shell脚本调用oracle命令:
1、直接执行Oracle命令
su - oracle -c "export ORACLE_SID=${db_name};
echo \"select sysdate from dual;\"
| sqlplus ${user_name}/${passwd}
| grep ORA- > /tmp/checkDBstatus" > /tmp/outnull.tmp 2>&1
可简单的用上面的语句检查数据库是否可连接,用户名、密码是否正确
2、写sql文件,然后执行。
定义脚本文件sqlFile和执行日志文件logFile(用于判断执行是否成功)
创建数据库用户
echo "prompt" > ${sqlFile}
echo "prompt creating user" >> ${sqlFile}
echo "prompt ==============================" >> ${sqlFile}
echo "prompt" >> ${sqlFile}
echo "connect SYS/${ora_sid_dbaPWD} as sysdba" >> ${sqlFile}
echo "spool ${logFile}" >> ${sqlFile}
echo "set echo on" >> ${sqlFile}
echo "create user ${username}" >> ${sqlFile}
echo "identified by ${userPWD}" >> ${sqlFile}
echo "default tablespace ${tablespace_name}" >> ${sqlFile}
echo "temporary tablespace ${temp_tablespace_name}" >> ${sqlFile}
echo "profile DEFAULT;" >> ${sqlFile}
echo "commit;" >> ${sqlFile}
echo "/" >> ${sqlFile}
echo "grant connect to ${username} with admin option;" >> ${sqlFile}
echo "grant resource to ${username} with admin option;" >> ${sqlFile}
echo "grant create any table to ${username} with admin option;" >> ${sqlFile}
echo "grant create any view to ${username} with admin option;" >> ${sqlFile}
echo "grant select any dictionary to ${username} with admin option;" >> ${sqlFile}
echo "grant select any table to ${username} with admin option;" >> ${sqlFile}
echo "grant unlimited tablespace to ${username} with admin option;" >> ${sqlFile}
echo "commit;" >> ${sqlFile}
echo "/" >> ${sqlFile}
echo "spool off" >> ${sqlFile}
echo "exit;" >> ${sqlFile}
执行
oracle用户:$($oracleHome/bin/sqlplus /nolog @ ${sqlFile})
非oracle用户:su - oracle -c "\${oracleHome}/bin/sqlplus /nolog @ /tmp/tbs.sql"
判断执行结果
oraError=$(grep -c -i "User created" ${logFile})
oraError2=$(grep -c -i "Grant succeeded" ${logFile})
创建表空间
echo "connect SYS/${ora_sid_dbaPWD} as sysdba" > ${sqlFile}
echo "set echo on" >> ${sqlFile}
echo "spool ${logFile}" >> ${sqlFile}
echo "create tablespace ${tablespace_name}" >> ${sqlFile}
echo "datafile" >> ${sqlFile}
echo "'${tablespace_path}'" >> ${sqlFile}
echo "size" >> ${sqlFile}
echo "${tablespace_size}m" >> ${sqlFile}
#echo "autoextend on next ${autoextendValue}m" >> ${sqlFile}
echo "extent management local" >> ${sqlFile}
echo "/" >> ${sqlFile}
echo "spool off" >> ${sqlFile}
echo "exit;" >> ${sqlFile}