一开始的shell如下:
#!/bin/bash
export NLS_LANG="SIMPLIFIED CHINESE_CHINA".ZHS16GBK
$ORACLE_HOME/bin/sqlplus -S username/pwd > /dev/null 2>&1 <<EOF
set echo off feedback off trimspool on linesize 3000;
set term off verify off feedback off pagesize 0;
spool /home/oracle/expdate/xcmx$(date +%Y%m%d).csv;
select site_code|| ',' ||
'="'||ewbs_list_no || '"'|| ',' ||
'="'||bill_code || '"'|| ',' ||
out_piece|| ',' ||
count(pallet_code) over(partition by site_code, ewbs_list_no, bill_code)|| ',' ||
'="'||pallet_code || '"'|| ',' ||
vol|| ',' ||
weight|| ',' ||
pieces|| ',' ||
pallet_user_name|| ',' ||
pallet_user_code
from (select site_code,
ewbs_list_no,
bill_code,
out_piece,
pallet_code,
pallet_user_name,
pallet_user_code,
sum(son_vol) vol,
sum(son_weight) weight,
count(sub_bill_code) pieces
from t_bas_pallet
where create_time >=to_date(to_char(sysdate-1,'yyyy-mm-dd')|| ' 12:00:00', 'yyyy-mm-dd hh24:mi:ss')
and create_time < to_date(to_char(sysdate,'yyyy-mm-dd')|| ' 12:00:00', 'yyyy-mm-dd hh24:mi:ss')
and pallet_code is not null
and is_deleted = 0
and type in (1, 2)
group by site_code,
ewbs_list_no,
bill_code,
out_piece,
pallet_code,
pallet_user_name,
pallet_user_code)
order by site_code, ewbs_list_no, bill_code;
spool off;
spool /home/oracle/expdate/fjzymx$(date +%Y%m%d).csv;
select site_code|| ',' ||
'="'||pallet_code || '"'|| ',' ||
pallet_user_name|| ',' ||
pallet_user_code|| ',' ||
'="'||pallet_time || '"'|| ',' ||
sort_man_name|| ',' ||
sort_man_code|| ',' ||
'="'||sort_time || '"'|| ',' ||
trunc((sort_time-pallet_time)*24*60,2)||'分' from(
select site_code,
pallet_code,
pallet_user_name,
pallet_user_code,
pallet_time,
sort_man_name,
sort_man_code,
sort_time,
row_number() over(partition by pallet_code order by pallet_time desc) rn
from t_bas_sort_job
where create_time >=
to_date(to_char(sysdate-1,'yyyy-mm-dd')|| ' 12:00:00', 'yyyy-mm-dd hh24:mi:ss')
and create_time <
to_date(to_char(sysdate,'yyyy-mm-dd')|| ' 12:00:00', 'yyyy-mm-dd hh24:mi:ss')
and type in (1,2)
and is_deleted = 0) where rn=1 order by site_code,pallet_user_code;
spool off;
spool /home/oracle/expdate/xcjt$(date +%Y%m%d).csv;
select site_code|| ',' ||
pallet_user_code|| ',' ||
pallet_user_name|| ',' ||
count(pallet_code)|| ',' ||
sum(directs)
from (select site_code,
pallet_user_code,
pallet_user_name,
pallet_code,
count(distinct(next_site_name)) directs
from fdm.t_bas_pallet
where create_time >= add_months(trunc(sysdate, 'mm'),-1)
and create_time < trunc(sysdate, 'mm') + 1 / 2
and pallet_code is not null
and is_deleted = 0
and type in (1, 2)
group by site_code,
pallet_user_code,
pallet_user_name,
pallet_code)
group by site_code, pallet_user_code, pallet_user_name order by site_code,pallet_user_code;
quit
EOF
rm /home/oracle/expdate/xcmx$(date +%Y%m%d --date="-7 day").csv
rm /home/oracle/expdate/fjzymx$(date +%Y%m%d --date="-7 day").csv
rm /home/oracle/expdate/xcjt$(date +%Y%m%d --date="-7 day").csv
--文件需要可执行权限
使用crontab自动执行发现只有最后的删除部分是正常执行的了的,但是SQL导出部份并没有执行,但是手动执行一切正常,该输出的文件都输出了,该删除的动作也做了。就很奇怪。在调试几次后发现每次自动执行的时间都非常块(正常手动执行需要5分钟左右),于是就在想是不是SQL段根本就没有执行或者说执行报错。
于是问公司的老司机,老司机给了我他的一段导出数据shell,如下:
#!/bin/ksh
export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_SID=nedb1
export ORACLE_BASE=/u01
export ORACLE_HOME=/u01/oracle/product/11.2.0.4/db
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export NLS_LANG=AMERICAN_AMERICA.UTF8
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH:.
export DIAG=/u01/diag/rdbms/nedb/nedb1/trace
LOGF=/home/oracle/exp_data/spool_`date +"%Y%m%d%H"`.log
sqlplus -s username/pwd <<EOF>>$LOGF
set linesize 3000;
set heading off;
set feedback off;
set term off;
set pagesize 0;
set trimspool on;
spool /home/oracle/exp_data/anedbmay.csv
SELECT T.Ewbs_List_No/* as 交接单号*/|| ',' ||
n.task_no /*as 任务号*/|| ',' ||
d.ewb_no /*as 运单号*/|| ',' ||
(select s2.site_name from hs_basic_site s2 where s2.site_id = T.SITE_ID) /*as 出库网点*/|| ',' ||
(select s3.site_name from hs_basic_site s3 where s3.site_id = T.NEXT_SITE_ID) /*as 下一网点*/|| ',' ||
(select s4.site_name from hs_basic_site s4 where s4.site_id = ewb.send_site_id) /*as 寄件网点*/|| ',' ||
(select s5.site_name from hs_basic_site s5 where s5.site_id = s.first_center_site_id)/* as 寄件网点所属分拨*/|| ',' ||
ewb.calc_weight /*as 结算重量*/|| ',' ||
d.out_weight /*as 出站交接重量*/|| ',' ||
d.in_weight /*as 进站交接重量*/
FROM HS_EWBS_LIST T
left join hs_ewbs_list_detail d on d.ewbs_list_no = t.ewbs_list_no
left join hs_ewbs_list_no n on n.ewbs_list_no = t.ewbs_list_no
left join hs_opt_ewb ewb on ewb.ewb_no = d.ewb_no
left join hs_basic_site s on s.site_id = ewb.send_site_id
WHERE n.task_no is not null and n.task_no <> '0'
and t.created_time between to_date('2016-05-03 00:00:00','yyyy-mm-dd hh24:mi:ss')
and to_date('2016-06-02 23:59:59','yyyy-mm-dd hh24:mi:ss');
spool off
exit
EOF
经过对比,豁然顿悟。原来是shell的环境变量没有声明,导致sh执行的时候不认识sqlplus命令,于是修改shell,如下:
#!/bin/bash
export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_SID=anepridb
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/db_1/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export PATH=/u01/app/oracle/product/11.2.0/db_1/bin:/usr/sbin:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/oracle/bin
export NLS_LANG="SIMPLIFIED CHINESE_CHINA".ZHS16GBK
sqlplus -S username/pwd > /dev/null 2>&1 <<EOF
set echo off feedback off trimspool on linesize 3000;
set term off verify off feedback off pagesize 0;
spool /home/oracle/expdate/xcmx$(date +%Y%m%d).csv;
select site_code|| ',' ||
'="'||ewbs_list_no || '"'|| ',' ||
'="'||bill_code || '"'|| ',' ||
out_piece|| ',' ||
count(pallet_code) over(partition by site_code, ewbs_list_no, bill_code)|| ',' ||
'="'||pallet_code || '"'|| ',' ||
vol|| ',' ||
weight|| ',' ||
pieces|| ',' ||
pallet_user_name|| ',' ||
pallet_user_code
from (select site_code,
ewbs_list_no,
bill_code,
out_piece,
pallet_code,
pallet_user_name,
pallet_user_code,
sum(son_vol) vol,
sum(son_weight) weight,
count(sub_bill_code) pieces
from t_bas_pallet
where create_time >=to_date(to_char(sysdate-1,'yyyy-mm-dd')|| ' 12:00:00', 'yyyy-mm-dd hh24:mi:ss')
and create_time < to_date(to_char(sysdate,'yyyy-mm-dd')|| ' 12:00:00', 'yyyy-mm-dd hh24:mi:ss')
and pallet_code is not null
and is_deleted = 0
and type in (1, 2)
group by site_code,
ewbs_list_no,
bill_code,
out_piece,
pallet_code,
pallet_user_name,
pallet_user_code)
order by site_code, ewbs_list_no, bill_code;
spool off;
spool /home/oracle/expdate/fjzymx$(date +%Y%m%d).csv;
select site_code|| ',' ||
'="'||pallet_code || '"'|| ',' ||
pallet_user_name|| ',' ||
pallet_user_code|| ',' ||
'="'||pallet_time || '"'|| ',' ||
sort_man_name|| ',' ||
sort_man_code|| ',' ||
'="'||sort_time || '"'|| ',' ||
trunc((sort_time-pallet_time)*24*60,2)||'分' from(
select site_code,
pallet_code,
pallet_user_name,
pallet_user_code,
pallet_time,
sort_man_name,
sort_man_code,
sort_time,
row_number() over(partition by pallet_code order by pallet_time desc) rn
from t_bas_sort_job
where create_time >=
to_date(to_char(sysdate-1,'yyyy-mm-dd')|| ' 12:00:00', 'yyyy-mm-dd hh24:mi:ss')
and create_time <
to_date(to_char(sysdate,'yyyy-mm-dd')|| ' 12:00:00', 'yyyy-mm-dd hh24:mi:ss')
and type in (1,2)
and is_deleted = 0) where rn=1 order by site_code,pallet_user_code;
spool off;
spool /home/oracle/expdate/zrjx$(date +%Y%m%d).csv;
select siteCode || ',' || sortUserName || ',' || sortUserCode || ',' ||sortTime || ',' || commissionNumber
from (select temp2.siteCode,
temp2.sortUserName,
temp2.sortUserCode,
temp2.sortTime,
temp2.commissionNumber + NVL(TBPU.DIFFERENCE, 0) commissionNumber
from (select tbpc.site_code siteCode,
tbpc.SORT_USER_NAME sortUserName,
tbpc.SORT_USER_CODE sortUserCode,
tbpc.SORT_TIME sortTime,
tbpc.COMMISSION_NUMBER commissionNumber
from t_bas_perf_count tbpc
where tbpc.SORT_TIME BETWEEN
TO_DATE(to_char(sysdate - 1, 'yyyy-mm-dd'),
'yyyy-mm-dd') AND
TO_DATE(to_char(sysdate - 1, 'yyyy-mm-dd'),
'yyyy-mm-dd')) temp2
left join (select SITE_CODE,
SORT_USER_NAME,
SORT_USER_CODE,
trunc(sysdate - 1, 'dd') sort_time,
sum(MODIFICATION_NUMBER - COMMISSION_NUMBER) difference
from t_bas_performance
where SORT_TIME BETWEEN
TO_DATE(to_char(sysdate - 1, 'yyyy-mm-dd') ||
' 00:00:00',
'yyyy-mm-dd hh24:mi:ss') AND
TO_DATE(to_char(sysdate - 1, 'yyyy-mm-dd') ||
' 23:59:59',
'yyyy-mm-dd hh24:mi:ss')
and modification_flag = 2
group by site_code,
sort_user_code,
sort_user_name,
to_date(to_char(SORT_TIME, 'yyyy-MM-dd'),
'yyyy-MM-dd')) tbpu
on temp2.SITECODE = tbpu.site_code
and temp2.sortUserCode = tbpu.SORT_USER_CODE
and temp2.sortTime = tbpu.sort_time
order by temp2.siteCode, temp2.sortUserCode) zrjx;
spool off;
spool /home/oracle/expdate/xcjt$(date +%Y%m%d).csv;
select site_code|| ',' ||
pallet_user_code|| ',' ||
pallet_user_name|| ',' ||
count(pallet_code)|| ',' ||
sum(directs)
from (select site_code,
pallet_user_code,
pallet_user_name,
pallet_code,
count(distinct(next_site_name)) directs
from fdm.t_bas_pallet
where create_time >= add_months(trunc(sysdate, 'mm'),-1)
and create_time < trunc(sysdate, 'mm') + 1 / 2
and pallet_code is not null
and is_deleted = 0
and type in (1, 2)
group by site_code,
pallet_user_code,
pallet_user_name,
pallet_code)
group by site_code, pallet_user_code, pallet_user_name order by site_code,pallet_user_code;
spool off;
quit
EOF
rm /home/oracle/expdate/xcmx$(date +%Y%m%d --date="-7 day").csv
rm /home/oracle/expdate/fjzymx$(date +%Y%m%d --date="-7 day").csv
rm /home/oracle/expdate/xcjt$(date +%Y%m%d --date="-7 day").csv
rm /home/oracle/expdate/zrjx$(date +%Y%m%d --date="-7 day").csv
再次测试,一切正常了。
参考:http://blog.itpub.net/519536/viewspace-621602