记一次自动导出CVS配置始末

一开始的shell如下：

#!/bin/bashexport NLS_LANG="SIMPLIFIED CHINESE_CHINA".ZHS16GBK$ORACLE_HOME/bin/sqlplus -S username/pwd  > /dev/null 2>&1  <<EOFset 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_jobwhere 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;quitEOFrm /home/oracle/expdate/xcmx$(date +%Y%m%d --date="-7 day").csvrm /home/oracle/expdate/fjzymx$(date +%Y%m%d --date="-7 day").csvrm /home/oracle/expdate/xcjt$(date +%Y%m%d --date="-7 day").csv

--文件需要可执行权限

使用crontab自动执行发现只有最后的删除部分是正常执行的了的，但是SQL导出部份并没有执行，但是手动执行一切正常，该输出的文件都输出了，该删除的动作也做了。就很奇怪。在调试几次后发现每次自动执行的时间都非常块（正常手动执行需要5分钟左右），于是就在想是不是SQL段根本就没有执行或者说执行报错。

于是问公司的老司机，老司机给了我他的一段导出数据shell，如下：

#!/bin/kshexport TMP=/tmpexport TMPDIR=$TMPexport ORACLE_SID=nedb1export ORACLE_BASE=/u01export ORACLE_HOME=/u01/oracle/product/11.2.0.4/dbexport LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/libexport CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlibexport NLS_LANG=AMERICAN_AMERICA.UTF8export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH:.export DIAG=/u01/diag/rdbms/nedb/nedb1/traceLOGF=/home/oracle/exp_data/spool_`date +"%Y%m%d%H"`.logsqlplus -s username/pwd <<EOF>>$LOGFset linesize 3000;set heading off;set feedback off;set term off;set pagesize 0;set trimspool on; spool /home/oracle/exp_data/anedbmay.csvSELECT 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 offexitEOF

经过对比，豁然顿悟。原来是shell的环境变量没有声明，导致sh执行的时候不认识sqlplus命令，于是修改shell，如下：

#!/bin/bashexport TMP=/tmpexport TMPDIR=$TMPexport ORACLE_SID=anepridbexport ORACLE_BASE=/u01/app/oracleexport ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1export LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/db_1/lib:/lib:/usr/libexport CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlibexport 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/binexport NLS_LANG="SIMPLIFIED CHINESE_CHINA".ZHS16GBKsqlplus -S username/pwd  > /dev/null 2>&1  <<EOFset 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_jobwhere 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;quitEOFrm /home/oracle/expdate/xcmx$(date +%Y%m%d --date="-7 day").csvrm /home/oracle/expdate/fjzymx$(date +%Y%m%d --date="-7 day").csvrm /home/oracle/expdate/xcjt$(date +%Y%m%d --date="-7 day").csvrm /home/oracle/expdate/zrjx$(date +%Y%m%d --date="-7 day").csv

再次测试，一切正常了。
参考：http://blog.itpub.net/519536/viewspace-621602