通过sh 脚本将数据库表 生成csv文件并压缩上传ftp
source /etc/profile
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
YYYY=`date "+%Y"`
MM=`date "+%m"`
DD=`date "+%d"`
DEL_DAY=`date -d "1 days ago" "+%d"`
del_file="*$YYYY$MM$DEL_DAY.csv"
daydir="$YYYY$MM$DD"
deldir="$YYYY$MM$DEL_DAY"
put_gz="*.gz"
echo $datafile
basepath=$(cd `dirname $0`; pwd)
cd $basepath
#删除当前目录下已有的gz文件,如果不删除,后面分割做tar的时候,就会有问题
rm *.gz -f
#无线网络控制器
datafile_test="test.csv"
tar_test="test_"
sqlplus -s dbuser/dbpwd@servicename <<EOF
set colsep','
set echo off
set feedback off
set heading off
set pagesize 0
set linesize 1000
set numwidth 20
set termout off
set timing off
set trimout on
set trimspool on
set null NULL
spool $datafile_test
select 'sid,sname' from dual;
select sid||','||sname from test;
spool off;
EOF
tar czf - $datafile_test|split -d -a 1 -b 200m - $tar_test&&ls|grep $tar_test |xargs -n1 -i{} mv {} {}.tar.gz
ftp -inv 127.0.0.1 <<EOF
user username password
lcd $basepath
cd 要上传的ftp目录
mkdir $daydir #创建 新的日期目录
cd $daydir
mput $put_gz
cd ..
cd $deldir #删除昨天的数据
mdel *
cd ..
rmdir $deldir
bye
EOF
find *.csv -mtime +3 -exec rm -f {} \;