在写脚本执行oracle sql时,会用到spool工具,但是遇到一个问题就是,执行的sql会输出到屏显,但是我只需要输出到指定的文件,不需要屏显。
1.修改前
案例:
编写tmp.sh脚本,查看oracle实例状态和日志模式,将执行过程输出到/tmp/status.txt
[root@linux-vm tmp]# cat tmp.sh
#!/bin/sh
ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1
su - oracle -c "${ORACLE_HOME}/bin/sqlplus '/as sysdba'"<<EOF1
spool /tmp/status.txt
select status from v\$instance;
select name,log_mode from v\$database;
spool off
exit
EOF1
执行脚本:
[root@linux-vm tmp]# ./tmp.sh
SQL*Plus: Release 11.2.0.1.0 Production on Fri Jun 19 16:59:51 2020
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SQL>
STATUS
------------
OPEN
SQL>
NAME LOG_MODE
--------- ------------
ORCL ARCHIVELOG
SQL> SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[root@linux-vm tmp]#
屏显会有这么一坨。。。其实只需要加个重定向就可以了
2.修改后
脚本:
#!/bin/sh
ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1
su - oracle -c "${ORACLE_HOME}/bin/sqlplus '/as sysdba'" <<EOF1 >/dev/null 2>&1
spool /tmp/status.txt
select status from v\$instance;
select name,log_mode from v\$database;
spool off
exit
EOF1
执行结果:
[root@linux-vm no_time_bak_0617]# ./tmp.sh
没有任何屏显,查看执行过程:
[root@linux-vm tmp]# cat /tmp/status.txt
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> select name,log_mode from v$database;
NAME LOG_MODE
--------- ------------
ORCL ARCHIVELOG
SQL> spool off
[root@linux-vm tmp]#
完