shell脚本与Oracle交互方式

http://blog.csdn.net/liuxigiant/article/details/9090965

shell脚本调用oracle命令：

1、直接执行Oracle命令

[plain] view plain copy print ?

1. su - oracle -c "export ORACLE_SID=${db_name};   
2.        echo \"select sysdate from dual;\"  
3.        | sqlplus ${user_name}/${passwd}   
4.        | grep ORA- > /tmp/checkDBstatus" > /tmp/outnull.tmp 2>&1  

[plain] view plain copy print ?

1. su - oracle -c "export ORACLE_SID=${db_name};   
2.        echo \"select sysdate from dual;\"  
3.        | sqlplus ${user_name}/${passwd}   
4.        | grep ORA- > /tmp/checkDBstatus" > /tmp/outnull.tmp 2>&1  

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（用于判断执行是否成功）

[plain] view plain copy print ?

1. 创建数据库用户  
2. echo "prompt" > ${sqlFile}  
3. echo "prompt creating user" >> ${sqlFile}  
4. echo "prompt ==============================" >> ${sqlFile}  
5. echo "prompt" >> ${sqlFile}  
6. echo "connect SYS/${ora_sid_dbaPWD} as sysdba" >> ${sqlFile}  
7. echo "spool ${logFile}" >> ${sqlFile}  
8. echo "set echo on" >> ${sqlFile}  
9. echo "create user ${username}" >> ${sqlFile}  
10. echo "identified by ${userPWD}" >> ${sqlFile}  
11. echo "default tablespace ${tablespace_name}" >> ${sqlFile}  
12. echo "temporary tablespace ${temp_tablespace_name}" >> ${sqlFile}  
13. echo "profile DEFAULT;" >> ${sqlFile}  
14. echo "commit;" >> ${sqlFile}  
15. echo "/" >> ${sqlFile}  
16. echo "grant connect to ${username} with admin option;" >> ${sqlFile}  
17. echo "grant resource to ${username} with admin option;" >> ${sqlFile}  
18. echo "grant create any table to ${username} with admin option;" >> ${sqlFile}  
19. echo "grant create any view to ${username} with admin option;" >> ${sqlFile}  
20. echo "grant select any dictionary to ${username} with admin option;" >> ${sqlFile}  
21. echo "grant select any table to ${username} with admin option;" >> ${sqlFile}  
22. echo "grant unlimited tablespace to ${username} with admin option;" >> ${sqlFile}  
23. echo "commit;" >> ${sqlFile}  
24. echo "/" >> ${sqlFile}  
25. echo "spool off" >> ${sqlFile}  
26. echo "exit;" >> ${sqlFile}  
27.   

[plain] view plain copy print ?

1. 创建数据库用户  
2. echo "prompt" > ${sqlFile}  
3. echo "prompt creating user" >> ${sqlFile}  
4. echo "prompt ==============================" >> ${sqlFile}  
5. echo "prompt" >> ${sqlFile}  
6. echo "connect SYS/${ora_sid_dbaPWD} as sysdba" >> ${sqlFile}  
7. echo "spool ${logFile}" >> ${sqlFile}  
8. echo "set echo on" >> ${sqlFile}  
9. echo "create user ${username}" >> ${sqlFile}  
10. echo "identified by ${userPWD}" >> ${sqlFile}  
11. echo "default tablespace ${tablespace_name}" >> ${sqlFile}  
12. echo "temporary tablespace ${temp_tablespace_name}" >> ${sqlFile}  
13. echo "profile DEFAULT;" >> ${sqlFile}  
14. echo "commit;" >> ${sqlFile}  
15. echo "/" >> ${sqlFile}  
16. echo "grant connect to ${username} with admin option;" >> ${sqlFile}  
17. echo "grant resource to ${username} with admin option;" >> ${sqlFile}  
18. echo "grant create any table to ${username} with admin option;" >> ${sqlFile}  
19. echo "grant create any view to ${username} with admin option;" >> ${sqlFile}  
20. echo "grant select any dictionary to ${username} with admin option;" >> ${sqlFile}  
21. echo "grant select any table to ${username} with admin option;" >> ${sqlFile}  
22. echo "grant unlimited tablespace to ${username} with admin option;" >> ${sqlFile}  
23. echo "commit;" >> ${sqlFile}  
24. echo "/" >> ${sqlFile}  
25. echo "spool off" >> ${sqlFile}  
26. echo "exit;" >> ${sqlFile}  

  创建数据库用户
  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}
  
 

[plain] view plain copy print ?

1.    

[plain] view plain copy print ?

1.    

[plain] view plain copy print ?

1. 执行  
2.  oracle用户：$($oracleHome/bin/sqlplus /nolog @ ${sqlFile})  

[plain] view plain copy print ?

1. 执行  
2.  oracle用户：$($oracleHome/bin/sqlplus /nolog @ ${sqlFile})  

 执行
  oracle用户：$($oracleHome/bin/sqlplus /nolog @ ${sqlFile})
 

[plain] view plain copy print ?

1. 非oracle用户：su - oracle -c "\${oracleHome}/bin/sqlplus /nolog @ /tmp/tbs.sql"  
2.   

[plain] view plain copy print ?

1. 非oracle用户：su - oracle -c "\${oracleHome}/bin/sqlplus /nolog @ /tmp/tbs.sql"  

  非oracle用户：su - oracle -c "\${oracleHome}/bin/sqlplus /nolog @ /tmp/tbs.sql"
  
 

[plain] view plain copy print ?

1.    

[plain] view plain copy print ?

1.    

[plain] view plain copy print ?

1. 判断执行结果  
2.  oraError=$(grep -c -i "User created" ${logFile})  
3.  oraError2=$(grep -c -i "Grant succeeded" ${logFile})  
4.    
5.    
6.    

[plain] view plain copy print ?

1. 判断执行结果  
2.  oraError=$(grep -c -i "User created" ${logFile})  
3.  oraError2=$(grep -c -i "Grant succeeded" ${logFile})  
4.    
5.    
6.    

 判断执行结果
  oraError=$(grep -c -i "User created" ${logFile})
  oraError2=$(grep -c -i "Grant succeeded" ${logFile})
  
  
  

[plain] view plain copy print ?

1.    

[plain] view plain copy print ?

1.    

[plain] view plain copy print ?

1. 创建表空间  
2.   echo "connect SYS/${ora_sid_dbaPWD} as sysdba" > ${sqlFile}  
3.   echo "set echo on" >> ${sqlFile}  
4.   echo "spool ${logFile}" >> ${sqlFile}  
5.   echo "create tablespace ${tablespace_name}" >> ${sqlFile}  
6.   echo "datafile" >> ${sqlFile}  
7.   echo "'${tablespace_path}'" >> ${sqlFile}  
8.   echo "size" >> ${sqlFile}  
9.   echo "${tablespace_size}m" >> ${sqlFile}  
10.   #echo "autoextend on next ${autoextendValue}m" >> ${sqlFile}  
11.   echo "extent management local" >> ${sqlFile}  
12.   echo "/" >> ${sqlFile}  
13.   echo "spool off" >> ${sqlFile}  
14.   echo "exit;" >> ${sqlFile}  

[plain] view plain copy print ?

1. 创建表空间  
2.   echo "connect SYS/${ora_sid_dbaPWD} as sysdba" > ${sqlFile}  
3.   echo "set echo on" >> ${sqlFile}  
4.   echo "spool ${logFile}" >> ${sqlFile}  
5.   echo "create tablespace ${tablespace_name}" >> ${sqlFile}  
6.   echo "datafile" >> ${sqlFile}  
7.   echo "'${tablespace_path}'" >> ${sqlFile}  
8.   echo "size" >> ${sqlFile}  
9.   echo "${tablespace_size}m" >> ${sqlFile}  
10.   #echo "autoextend on next ${autoextendValue}m" >> ${sqlFile}  
11.   echo "extent management local" >> ${sqlFile}  
12.   echo "/" >> ${sqlFile}  
13.   echo "spool off" >> ${sqlFile}  
14.   echo "exit;" >> ${sqlFile}