大部分真实生产环境中的服务器都是linux，oracle DBA工作过程中有时会遇到通过shell脚本批量执行sql脚本完成某一项任务,最典型的例子是oracle DBCA创建的数据库脚本就是这种模式，适宜环境下稍加修改便可完全通过脚本完成新库的安装，小编在这里将简单两种应用样例列举如下，方便以后工作.
---------------------------------------shell文件和sql文件组合样例---------------------------------------------
---------linux中的执行shell文件中包含了sql内容：
[oracle@CentOS5 ~]$ cat spool.sh
#!/bin/sh
OLD_UMASK=`umask`
umask 0027
umask ${OLD_UMASK}
ORACLE_SID=taxi; export ORACLE_SID
PATH=$ORACLE_HOME/bin:$PATH; export PATH
/oracle/product/10.2.0/db_1/bin/sqlplus / as sysdba <<EOF
set linesize 120
set pagesize 100
spool /home/oracle/spool.txt
select * from scott.dept;
spool off
exit;
EOF
[oracle@CentOS5 ~]$
------->>执行情况:
[oracle@CentOS5 ~]$ sh spool.sh
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Oct 23 17:43:00 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> SQL> SQL> SQL>
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
---------------------------------------shell文件和sql文件分开样例---------------------------------------------
---------linux中的执行shell文件内容：
[oracle@CentOS5 ~]$ cat test.sh
#!/bin/sh
OLD_UMASK=`umask`
umask 0027
umask ${OLD_UMASK}
ORACLE_SID=taxi; export ORACLE_SID
PATH=$ORACLE_HOME/bin:$PATH; export PATH
/oracle/product/10.2.0/db_1/bin/sqlplus / as sysdba @/home/oracle/test.sql
[oracle@CentOS5 ~]$
---------sql文件中内容:
[oracle@CentOS5 ~]$ cat test.sql
set linesize 120
set pagesize 120
spool /home/oracle/test.txt
select * from scott.emp;
spool off;
exit;
--------->>执行情况:
[oracle@CentOS5 ~]$ sh test.sh
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Oct 23 17:43:06 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@CentOS5 ~]$
DBCA创建新库脚本追踪学习案例请参见我的另外一篇博文:http://blog.itpub.net/29119536/viewspace-1300706/
linux下通过sql文件批量操作脚本样本
原文：http://blog.itpub.net/29119536/viewspace-1308290/
