linux下通过sql文件批量操作脚本样本

    大部分真实生产环境中的服务器都是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 < set linesize 120
set pagesize 100
spool  /home/oracle/spool.txt
select * from scott.dept;
spool off
exit;
EOF
[oracle@CentOS5 ~]$
-------&gt>执行情况:
[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;

---------&gt>执行情况:
[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/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29119536/viewspace-1308290/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29119536/viewspace-1308290/