Bash shell and SQLPLUS

时间：2010-06-04 来源：star_zhang

Bash shell and SQLPLUS

l
关键字：

Bash shell:
shell脚本的一种，该文档中shell指的都是bash shell。
SQLPLUS： oracle的SQL查询的交互工具。

l
序言：

Shell脚本和SQLPLUS的完美结合为数据库工作人员节省了很多维护的工作，也减少了人工操作错误的产生，鉴于现在的书籍大部分介绍shell或者是sqlplus，却没有两者结合的这样的文档，因此根据个人的所学的知识和经验，写了这份文档，不妥之处敬请指正。

l
SQLPLUS常用命令和使用：
1．
使用脚本调用SQLPLUS工具，一般使用SQLPLUS的静默方式。
2．
常用的SQLPLUS输出格式化命令：
a.
执行一个SQL脚本文件

SQL>start
file_name
SQL>@ file_name
b.
将显示的内容输出到指定文件

SQL>SPOOL file_name
c.
关闭spool输出
SQL> SPOOL OFF
只有关闭spool输出，才会在输出文件中看到输出的内容。
d.
改变列的显示长度.
字符型改变：
SQL> colcolname format a40
数值型改变：

SQL> colcolname format 99999.99
e.
是否显示脚本中正在执行的SQL语句.

SQL> SET ECHO{ON|OFF}
f.
是否显示当前sql语句查询或修改的行数.
SQL>SET FEED[BACK] {6|n|ON|OFF}

默认只有结果大于6行时才显示结果的行数。如果set feedback 1 ，
则不管查询到多少行都返回。当为off 时，一律不显示查询的行数
g.
是否显示列标题

SQL> SET HEA[DING] {ON|OFF}
当set heading off 时，在每页的上面不显示列标题，而是以空白行代替
h.
设置一行可以容纳的字符数

SQL> SETLIN[ESIZE] {80|n}

如果一行的输出内容大于设置的一行可容纳的字符数，则折行显示。
i.
设置页与页之间的分隔

SQL> SET NEWP[AGE] {1|n|NONE}
当set newpage 0 时，会在每页的开头有一个小的黑方框。
当set newpage n 时，会在页和页之间隔着n个空行。
当set newpage none 时，会在页和页之间没有任何间隔。
j.
设置一页有多少行数

SQL>SET PAGES[IZE] {24|n}
如果设为0，则所有的输出内容为一页并且不显示列标题
k.
是否显示用DBMS_OUTPUT.PUT_LINE包进行输出的信息
SQL> SETSERVEROUT[PUT] {ON|OFF}

这个在shell脚本中经常使用，默认buffer是256个字节。

当输出的大于256时，可以使用SET SERVEROUT onsize 1000000;

最大buffer的缓冲区为1000000（9i and 8i）。
l.
当SQL语句的长度大于LINESIZE时，是否在显示时截取SQL语句
SQL> SETWRA[P] {ON|OFF}

当输出的行的长度大于设置的行的长度时（用set linesize n命令设置），
当setwrap on时，输出行的多于的字符会另起一行显示，否则，
会将输出行的多于字符切除，不予显示。
m.
是否在屏幕上显示输出的内容，主要用与SPOOL结合使用
SQL> SETTERM[OUT] {ON|OFF}

在用spool命令将一个大表中的内容输出到一个文件中时，
将内容输出在屏幕上会耗费大量的时间，设置set termspool off后，
则输出的内容只会保存在输出文件中，不会显示在屏幕上，
极大的提高了spool的速度。
n.
将SPOOL输出中每行后面多余的空格去掉

SQL>SET TRIMS[OUT] {ON|OFF}
o.
显示每个sql语句花费的执行时间

SQL>set TIMING  {ON|OFF}

l
经常使用到的shell 命令：
1．
Awk

awk是一种编程语言，用于在linux/unix下对文本和数据进行处理。
(A) awk常用命令选项：
-F fs or--field-separator fs
指定输入文件折分隔符，fs是一个字符串或者是一个正则表达式，如-F:。
-v var=value or--asign var=value
赋值一个用户定义变量。

(B) awk常用的环境变量。

FILENAME 当前文件名。

FS
字段分隔符(默认是任何空格)。

NF 当前记录中的字段数。

NR 当前记录数。

OFS 输出字段分隔符(默认值是一个空格)。

ORS 输出记录分隔符(默认值是一个换行符)。

$0
完整的输入记录。

$n
当前记录的第n个字段，字段间由FS分隔。

(C)awk的代码事例：

例一：

输入” 245358,257084”,希望获得去掉逗号的数字串。

echo "245358,257084"|awk'BEGIN{FS=",";ORS=""}{for(i=1;i<=NF;i++)print $i}'

获得的结果：

245358257084
例二：

awk
-F':' '{
print FILENAME}'
/etc/passwd

获得结果：

/etc/passwd

/etc/passwd

…………..
例三：

awk
-F':' '{
print NF}'
/etc/passwd

获得结果：7

2．
Sed:

sed是一种在线编辑器，它一次处理一行内容。
(A)
Sed常用命令选项：

-e command, --expression=command
允许多台编辑。
-n, --quiet,--silent
取消默认输出。
-f,--filer=script-file
引导sed脚本文件名。
(B)
Sed常用的参数。

a\ 在当前行后面加入一行文本

c\ 用新的文本改变本行的文本
i\ 在当前行上面插入文本
d 删除行

s/re/string

用string替换re。

g 获得内存缓冲区的内容，并替代当前模板块中的文本。

p 打印模板块的行。
(C)
Sed的代码事例：

例一：

cp /etc/passwd
/tmp

sed '2d'
/tmp/passwd-----删除/tmp/passwd文件的第二行。
sed '2,$d' /tmp/passwd-----删除/tmp/passwd文件的第二行到末尾所有行。
sed '$d'
/tmp/passwd-----删除/tmp/passwd文件的最后一行。
sed '/oracle/'d /tmp/passwd-----删除/tmp/passwd文件所有包含oracle的行。

例二：

比如sqlmap 文件中有下列sql 语句，我们想把所有的select语句拿出来，
仅仅是sql已经并且加上个换行符。

文件内容如下：

cat /test.xml|sed -n -e '/<selectid=/,/<\/select>/p'|sed '/<select id=/d'|sed 's/<\/select>/\n/g'
获得结果：

select *from iw_mobile_convoy where (IW_USER_ID = #value#)

select *from beyond_card_sign where ((bank_card_no = #bankCardNo#) AND (email =#email#))

l
如何写shell和SQLPLUS程序：
一般的模式
sqlplus -S <<EOF

具体的脚本
EOF
一个简单spool数据的事例：
在tmp目录下产生data.lst文件

#!/bin/sh
Datapath=”/tmp/”
sqlplus -S "username/password@tnsname"<<EOF>/dev/null
SET FEEDBACK OFF;
SET HEADING ON;
SET ECHO OFF;
SET TERM OFF;
SET LINESIZE 255;
SET PAGESIZE 200;
COL
TABLE_NAME
FORMAT a30;
COL
TABLESPACE_NAME
FORMAT a30;
COL
NUM_ROWS FORMAT 99999999;

SPOOL
$Datapath /data;
select TABLE_NAME,TABLESPACE_NAME,nvl(NUM_ROWS,0)
as
NUM_ROWS from
user_tables where rownum<=100;
SPOOL OFF;
quit
EOF

l
事例精选：

功能：monitor数据库或者表，并发相关错误的mail。
工具的目录结构：

bin/

conf/
log/

配置参数代码如下：
# vi
onf/ monitorTool.cfg

###list the mailbox.
###If more than oneemail addresses, please use comma as list separator.
###Please use doublequotation marks to include all values
Mail_Box="[email protected]"

###Total Attemptnumber when can not connect the DB,the defual value is 3.
AttemptNumber=3

###Wait Time when cannot connnect the DB,the defual value is 1(minute)
WaitTime=1

###connect DB TNS
DBTNS="username/password@tnsname"

##############################################################
#
The following script, you do not needconfigure it! #
##############################################################
export
Mail_Box
export
AttemptNumber
export
WaitTime
export
DBTNS