sqlplus shell导出脚本

创建tb_a.sql文件

select id,name from tb_a;

创建tb_b.sql文件

select id,name from tb_b;

创建tb_a.sh
yest定义昨天的时间，yyyymmdd

#!/bin/sh
yest=`date +%D | awk -F/ '{s=$2-1}{printf"20%02d%02d%02d\n", $3,$1,s}'`
echo "sqlpath:$sqlpath/tb_a.sql,exporting:$Dir/tb_a$yest.dat"
sqlplus -S /nolog > log.log <<EOF
conn gdmcp/gdmcp
set verify off;
set wrap off;
set echo off;
set term off;
set heading off;
set feedback off;
set pagesize 0;
set linesize 1000;
set trimspool on;
spool $Dir/tb_a$yest.dat;
@$sqlpath/tb_a.sql;
spool off;
exit;
EOF

创建tb_b.sh

#!/bin/sh
yest=`date +%D | awk -F/ '{s=$2-1}{printf"20%02d%02d%02d\n", $3,$1,s}'`
echo "sqlpath:$sqlpath/tb_b.sql,exporting:$Dir/tb_b$yest.dat"
sqlplus -S /nolog > log.log <<EOF
conn gdmcp/gdmcp
set verify off;
set wrap off;
set echo off;
set term off;
set heading off;
set feedback off;
set pagesize 0;
set linesize 1000;
set trimspool on;
spool $Dir/tb_b$yest.dat;
@$sqlpath/tb_b.sql;
spool off;
exit;
EOF

创建export.sh

#!/bin/sh
Dir="/oracle/zhangjh/data";
export Dir;
sqlpath="/oracle/zhangjh";
export sqlpath;

./tb_a.sh
echo "导出A表信息完毕 日全量"
./tb_b.sh
echo "导出B表信息完毕 日全量"

必须在linux里安装了oracle客户端。