ITPub博客

首页 > 数据库 > Oracle > sqlldr+awk+shell的一次记录

sqlldr+awk+shell的一次记录

原创 Oracle 作者:czxin788 时间:2015-07-20 23:21:08 0 删除 编辑

 

近期,笔者想对公司服务器维保期的时间进行一个维护,也就是将维保要过期的服务器及时邮件通知系统管理员。其实在笔者的知识范围内,用awk实现最好不过,但是却对awk的两个日期比较,大为不解。于是准备将服务器维保期的excel表格导入oracle数据库进行处理,在结合linux shell+awk解决。

<!--more-->

 

现写此文以记之。

 

1、先将excel中空单元格按照下述方法,随便填充一个数据,这样做的目的是为了使excel表格的数据保持一致性。

1.1按下图,找到定位条件:

1.2如下操作:

 

1.3 然后在其中任一个空单元格中随便输入一个值,按ctl+Enter确认,即可将所有空单元格填充数据。

2、现用sqlldrexcel导入oracle,需要先在数据库中建立一张表,通过如下方法建立。

2.1 选取excel字段名,如下方式进行行列转换:

 

 

 

 

2.2 在字段名上加双引

可以改一下单元格格式,为自定义,""@""

 

 

 

2.3 生产建表语句

 

 

 

3、将excel表格另存为jwt.csv,上传到oracle服务器/home/oracle目录下。

4.1当改成以csv结尾的文件后,如想将其改成和linux系统兼容的utf8格式(这样文档中的中文就能在linux系统中显示了),如下:

 

 

 

 

 

4、使用sqlldr导入数据

4.1 /home/oracle目录下建立控制文件1.ctl

cat /home/oracle/1.ctl

LOAD DATA

INFILE '/home/oracle/jwt.csv' 

truncate  INTO TABLE jwt

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

(序号, 机柜管理员,    业务单位

联系人,    业务单位所属版块)

 

4.2 oracle用户下执行:

node1-> sqlldr scott/tiger control=/home/oracle/1.ctl bad=/home/oracle/bad.txt log=/home/oracle/log.txt  SKIP=1

执行上面的命令后,就会在oracle数据库中建立一张名字叫jwt的表。

 

5、编写shell脚本,满足条件的发邮件:

 

#!/bin/bash

sqlplus -s 'scott/tiger'<<EOF

spool /tmp/a.txt

set linesize 150

col gly for a10

col mail for a20

col xyname for a35

col ip for a25

set heading off

select * from jwt where (wbrq-sysdate)<90;

spool off

exit;

EOF

cat /tmp/a.txt |grep -v ^$ |awk -F" " '{print "echo -n 这是一封测试邮件   亲爱的"$1",""您维护的"$3",""ip地址为"$4", 服务器维保期将在"$5"过期,请您及时和业务单位联系>维保事宜,谢谢!""|""mail -s server_expired "$2 }' > /tmp/b.sh

/bin/bash /tmp/b.sh

 

 

小记:

如果发现邮件发不出去,检查/var/spool/mqueue目录下面是否有排队的邮件,并查看/var/log/maillog日志信息。我遇到发不出去邮件时,我重启一下服务就好了(service sendmail restart)。

 

完。

 

更新1
最近又重新做了一下。
SQL> desc jwt;
 GLY                                                                                                                                                                   VARCHAR2(100)
 MAIL                                                                                                                                                                  VARCHAR2(100)
 XYNAME                                                                                                                                                                VARCHAR2(200)
 IP                                                                                                                                                                    VARCHAR2(100)
 WBRQ                                                                                                                                                                  VARCHAR2(100)


[oracle@centos-msgw2 ~]$ head jwt1.csv
gly,mail,xyname,ip,wbrq
xx,xx@hn.com,xx
系统,10.99.32.133,2013/7/21


[oracle@centos-msgw2 ~]$ cat 1.ctl
 
LOAD DATA
INFILE '/home/oracle/jwt1.csv'
truncate
 INTO TABLE jwt
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
trailing nullcols
(gly ,mail,xyname,ip,wbrq)



[oracle@centos-msgw2 ~]$ sqlldr scott/tiger control=/home/oracle/1.ctl bad=/home/oracle/bad.txt log=/home/oracle/log.txt  SKIP=1 

[oracle@centos-msgw2 ~]$ cat mail.sh
 
#!/bin/bash

sqlplus -s 'scott/tiger'<<EOF

set linesize 1000

col gly for a15

col mail for a25

col xyname for a50

col ip for a28

set heading off

spool /tmp/a.txt

select  * from jwt where (to_date(wbrq,'yyyy/mm/dd')-sysdate)<30 and (to_date(wbrq,'yyyy/mm/dd')-sysdate)>0 ;

spool off

exit;

EOF

cat /tmp/a.txt |grep -v "^$" |grep -v select |awk -F" " '{print "echo -n    亲爱的"$1",""您维护的"$3",""ip地址为"$4", 服务器维保期将在"$5"过期,请您及时和业务单位联系维保事宜,谢谢""|""mail -s server_expired "$2,"zhix_chen@hnair.com","lfang_wu@hnair.com" }' > /tmp/b.sh

sed -i 's/亲爱的/"亲爱的/' /tmp/b.sh

sed -i 's/谢谢/谢谢"/' /tmp/b.sh

/bin/bash /tmp/b.sh

 

更新2

2015720日星期一

 

    今天,我又做了一次服务器维保过期告警提醒。这次我完全用awk做的。

    数据的excel表是这样的:

   

 

    然后将起保存中csv格式,然后再将这个csv的文件,右键点击编辑打开后,再另存一下,将其编码格式转换为utf8.最后将这个csv文件上传到linux目录下。我的文件名为/home/oracle/jwt0720.csv

    对了,注意上面截图中的时间戳一列,我是将维保日期通过excel转化成了时间戳,这样是为了方便在linux中通过awk进行时间的运算。

 

    EXCEL中把日期转为unix时间戳方法:INT((B2-70*365-19)*86400-8*3600)

还有一点,就是我为了判断excel里面有通过ALT+Enter换行的单元格,我先把上面csv表格通过sqlldr导入进oracle数据库,然后查看bad.txt的行,这些行说明有问题,然后我再一一手工到csv表格中去对比分析,看是什么问题并改正。

 

create table jwt (

gly varchar2(255),

mail varchar2(255),

xyname varchar2(255),

ip varchar2(50),

jw varchar2(100),

jixing varchar2(100),

xulh varchar2(255),

wbrq varchar2(100)

);

 

 

[oracle@centos-msgw2 ~]$ cat 1.ctl

LOAD DATA

INFILE '/home/oracle/jwt0720.csv'

truncate  INTO TABLE jwt

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

trailing nullcols  ##如要导入源文件此列内容为空,在导入到数据库表中,此列内容就是null

(

gly,

mail,

xyname,

ip,

jw,

jixing,

xulh,

wbrq

)

 

sqlldr scott/tiger control=/home/oracle/1.ctl bad=/home/oracle/bad.txt log=/home/oracle/log.txt  SKIP=1

 

 

 

    下面开始在linux上编写脚本:

 

#!/bin/bash

#将当前时间换算成时间戳

shijianchuo=`date +%s`

#通过时间戳,判断最近1个月内维保期将要过期的服务器

awk -F',' '($9-'$shijianchuo')/3600/24 < 30 && ($9-'$shijianchuo')/3600/24 > 0 {print $0}' jwt0720.csv > /tmp/a.txt

cat /tmp/a.txt |grep -v "^$"  |awk -F',' '{print "echo -n    Hello,"$1",""您维护的"$3",""ip地址为"$4", 服务器维保期将在"$8"日过期,请您及时和业务单位联系维保事宜,谢谢。该服务器的型号为"$6",序列号为"$7",机器位于北方数据中心"$5"。此邮件为系统自动发送,请勿回复。""|""mail -s 服务器维保过期提醒 "$2,"zhix_chen@hnair.com","lfang_wu@hnair.com" }' > /tmp/b.sh

sed -i 's/Hello/"Hello/' /tmp/b.sh

sed -i 's/回复。/回复。"/' /tmp/b.sh

chmod +x /tmp/b.sh

/bin/bash /tmp/b.sh

 



来自为知笔记(Wiz)


附件列表

     

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

    上一篇: 调优的学习笔记
    请登录后发表评论 登录
    全部评论

    注册时间:2014-06-03

    • 博文量
      185
    • 访问量
      584540