Bash shell: shell脚本的一种,该文档中shell指的都是bash shell。
SQLPLUS: oracle的SQL查询的交互工具。
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> col colname format a40
数值型改变:
SQL> col colname 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> SET LIN[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> SET SERVEROUT[PUT] {ON|OFF}
这个在shell脚本中经常使用,默认buffer是256个字节。
当输出的大于256时,可以使用SET SERVEROUT on size 1000000;
最大buffer的缓冲区为1000000(9i and 8i)。
l. 当SQL语句的长度大于LINESIZE时,是否在显示时截取SQL语句
SQL> SET WRA[P] {ON|OFF}
当输出的行的长度大于设置的行的长度时(用set linesize n命令设置),
当set wrap on时,输出行的多于的字符会另起一行显示,否则,
会将输出行的多于字符切除,不予显示。
m. 是否在屏幕上显示输出的内容,主要用与SPOOL结合使用
SQL> SET TERM[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 '/<select id=/,/<//select>/p'|sed '/<select id=/d'|sed 's/<//select>//n/g'
获得结果:
select * from test_a where (a = #a#)
select * from test_a where ((a = #a#) AND (b = #b#))
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
功能:monitor数据库或者表,并发相关错误的mail。
工具的目录结构:
bin/
conf/
log/
配置参数代码如下:
# vi onf/ monitorTool.cfg
###list the mailbox.
###If more than one email addresses, please use comma as list separator.
###Please use double quotation marks to include all values
Mail_Box="xxx@hotmail.com"
###Total Attempt number when can not connect the DB,the defual value is 3.
AttemptNumber=3
###Wait Time when can not connnect the DB,the defual value is 1(minute)
WaitTime=1
###connect DB TNS
DBTNS="username/password@tnsname"
##############################################################
# The following script, you do not need configure it! #
##############################################################
export Mail_Box
export AttemptNumber
export WaitTime
export DBTNS