shell脚本与Oracle交互方式

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}