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/，如需轉載，請註明出處，否則將追究法律責任。