1、执行oracle数据库中的存储过程
proc.sh脚本内容（IBM的AIX环境下）
?
ORACLE_BASE=/oracle;export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0;export ORACLE_HOME
ORACLE_SID=commdb;export ORACLE_SID
$ORACLE_HOME/bin/sqlplus cfa/cfa<<!
exec sp_B_20090827;
exit
!
2、备份文件名后缀为日期对数据库中的表进行exp备份
exp_tab_perday.sh脚本内容（IBM的AIX环境下）
?
DATE=`date +%Y%m%d_%T`;export DATE
ORACLE_BASE=/oracle;export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0;export ORACLE_HOME
ORACLE_SID=commdb;export ORACLE_SID
$ORACLE_HOME/bin/exp cfa/cfa file=/oracle/dlbk_table$DATE.dmp log=/oracle/dlbk_table$DATE.log tables=cfa_income_ent_new,cfa_income_inst_old,cfa_income_ent_old
3、对exp的用户备份，进行恢复
imp_user.sh脚本内容（RedHat AS4环境下）
?
export ORACLE_HOME=/opt/oracle/product/10g
$ORACLE_HOME/bin/imp file=/home/oracle/dlbk_cfa2009-09-12.dmp log=/home/oracle/dlbk_cfa2009-09-12.dmp fromuser=cfa touser=cfa
4、将oracle数据库中表的数据用spool导成标准的txt格式
调度脚本spool_out.sh的内容（IBM的AIX环境下）:
ORACLE_BASE=/oracle;export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0;export ORACLE_HOME
ORACLE_SID=commdb;export ORACLE_SID
DATE=`date +%Y%m%d_%T`;export DATE
$ORACLE_HOME/bin/sqlplus cfa/cfa @/oracle/cfaout/spool.sql
spool配置脚本spool.sql的内容
set heading on;
set feedback off;
set pagesize 0;
set linesize 8000;
set trimout on;
set trimspool on;
set term off;
SET NEWPAGE 1 ;
spool /oracle/cfaout/aix_ent_info_$DATE.txt;
@/oracle/cfaout/select_ent_info.sql;
spool off;
spool /oracle/cfaout/aix_report_record_$DATE.txt;
@/oracle/cfaout/select_report_record.sql;
spool off;
exit;
两select表成标准格式脚本内容：
select_ent_info.sql:
select CUSTOMERID||'|'||CORPID||'|'||ENTERPRISENAME||'|'||ENGLISHNAME||'|'||FICTITIOUSPERSON||'|'||ORGNATURE||'|'||FINANCETYPE||'|'||ENTERPRISEBELONG||'|'||INDUSTRYTYPE||'|'||INDUSTRYTYPE1||'|'||INDUSTRYTYPE2||'|'||PRIVATE||'|'||ECONOMYTYPE||'|'||ORGTYPE||'|'||MOSTBUSINESS||'|'||BUDGETTYPE||'|'||RCCURRENCY||'|'||REGISTERCAPITAL||'|'||PCCURRENCY||'|'||PAICLUPCAPITAL||'|'||FUNDSOURCE||'|'||TOTALASSETS||'|'||NETASSETS||'|'||ANNUALINCOME||'|'||SCOPE||'|'||LIMIT||'|'||CREDITDATE||'|'||LICENSENO||'|'||LICENSEDATE||'|'||LICENSEMATURITY||'|'||SETUPDATE||'|'||INSPECTIONYEAR||'|'||LOCKSITUATION||'|'||TAXNO||'|'||BANKLICENSE||'|'||BANKID||'|'||MANAGEAREA||'|'||BANCHAMOUNT||'|'||EXCHANGEID||'|'||REGISTERADD||'|'||CHARGEDEPARTMENT||'|'||OFFICEADD||'|'||OFFICEZIP||'|'||COUNTRYCODE||'|'||REGIONCODE||'|'||VILLAGECODE||'|'||VILLAGENAME||'|'||RELATIVETYPE||'|'||OFFICETEL||'|'||OFFICEFAX||'|'||WEBADD||'|'||EMAILADD||'|'||EMPLOYEENUMBER||'|'||MAINPRODUCTION||'|'||NEWTECHCORPORNOT||'|'||LISTINGCORPORNOT||'|'||HASIERIGHT||'|'||HASDIRECTORATE||'|'||BASICBANK||'|'||BASICACCOUNT||'|'||MANAGEINFO||'|'||CUSTOMERHISTORY||'|'||PROJECTFLAG||'|'||REALTYFLAG||'|'||WORKFIELDAREA||'|'||WORKFIELDFEE||'|'||ACCOUNTDATE||'|'||LOANCARDNO||'|'||LOANCARDPASSWORD||'|'||LOANCARDINSYEAR||'|'||LOANCARDINSRESULT||'|'||LOANFLAG||'|'||FINANCEORNOT||'|'||FINANCEBELONG||'|'||CREDITBELONG||'|'||CREDITLEVEL||'|'||EVALUATEDATE||'|'||OTHERCREDITLEVEL||'|'||OTHEREVALUATEDATE||'|'||OTHERORGNAME||'|'||INPUTORGID||'|'||INPUTUSERID||'|'||INPUTDATE||'|'||UPDATEORGID||'|'||UPDATEUSERID||'|'||UPDATEDATE||'|'||REMARK||'|'||TAXNO1||'|'||FICTITIOUSPERSONID||'|'||GROUPFLAG||'|'||EVALUATELEVEL||'|'||MYBANK||'|'||MYBANKACCOUNT||'|'||OTHERBANK||'|'||OTHERBANKACCOUNT||'|'||TEMPSAVEFLAG||'|'||FINANCEDEPTTEL||'|'||ECGROUPFLAG||'|'||SUPERCORPNAME||'|'||SUPERLOANCARDNO||'|'||SUPERCERTTYPE||'|'||SMEINDUSTRYTYPE||'|'||SELLSUM||'|'||SUPERCERTID||'|'||ISVOUCHCORP from aix_ent_info;
select_report_report.sql:
?
select REPORTNO||'|'||REPORTDATE||'|'||MODELNO||'|'||REPORTSCOPE||'|'||OBJECTNO from aix_report_record;
5、将标准的txt文件load进oracle数据库的shell脚本
sqlldr_in.sh（IBM的AIX环境下）
ORACLE_BASE=/oracle;export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0;export ORACLE_HOME
ORACLE_SID=commdb;export ORACLE_SID
DATE=`date +%Y%m%d`;export DATE
$ORACLE_HOME/bin/sqlldr cfa/cfa control=ENT_INFO.ctl log=ENT_INFO_$DATE.log rows=10000 readsize=20000000 bindsize=20000000
$ORACLE_HOME/bin/sqlldr cfa/cfa control=REPORT_CATALOG.ctl log=REPORT_CATALOG_$DATE.log rows=10000 readsize=20000000 bindsize=20000000
$ORACLE_HOME/bin/sqlldr cfa/cfa control=REPORT_DATA.ctl log=REPORT_DATA_$DATE.log rows=10000 readsize=20000000 bindsize=20000000
$ORACLE_HOME/bin/sqlldr cfa/cfa control=REPORT_RECORD.ctl log=REPORT_RECORD_$DATE.log rows=10000 readsize=20000000 bindsize=20000000
相关的四张表的控制文件的内容：
ENT_INFO.ctl：==
LOAD DATA
INFILE '/oracle/cfain/ENT_INFO_$DATE.txt'
TRUNCATE
INTO TABLE AIX_ENT_INFO
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
CUSTOMERID?????????? "nvl(:CUSTOMERID??????? ,'0')",??
CORPID?????????????? "nvl(:CORPID??????????? ,'0')",??
ENTERPRISENAME?????? "nvl(:ENTERPRISENAME??? ,'0')",??
ENGLISHNAME????????? "nvl(:ENGLISHNAME?????? ,'0')",??
FICTITIOUSPERSON???? "nvl(:FICTITIOUSPERSON ,'0')",??
ORGNATURE??????????? "nvl(:ORGNATURE???????? ,'0')",??
FINANCETYPE????????? "nvl(:FINANCETYPE?????? ,'0')",??
ENTERPRISEBELONG???? "nvl(:ENTERPRISEBELONG ,'0')",??
INDUSTRYTYPE???????? "nvl(:INDUSTRYTYPE????? ,'0')",??
INDUSTRYTYPE1??????? "nvl(:INDUSTRYTYPE1???? ,'0')",??
INDUSTRYTYPE2??????? "nvl(:INDUSTRYTYPE2???? ,'0')",??
PRIVATE????????????? "nvl(:PRIVATE?????????? ,'0')",??
ECONOMYTYPE????????? "nvl(:ECONOMYTYPE?????? ,'0')",??
ORGTYPE????????????? "nvl(:ORGTYPE?????????? ,'0')",??
MOSTBUSINESS???????? "nvl(:MOSTBUSINESS????? ,'0')",??
BUDGETTYPE?????????? "nvl(:BUDGETTYPE??????? ,'0')",??
RCCURRENCY?????????? "nvl(:RCCURRENCY??????? ,'0')",??
REGISTERCAPITAL????? "nvl(:REGISTERCAPITAL?? ,'0')",??
PCCURRENCY?????????? "nvl(:PCCURRENCY??????? ,'0')",??
PAICLUPCAPITAL?????? "nvl(:PAICLUPCAPITAL??? ,'0')",??
FUNDSOURCE?????????? "nvl(:FUNDSOURCE??????? ,'0')",??
TOTALASSETS????????? "nvl(:TOTALASSETS?????? ,'0')",??
NETASSETS??????????? "nvl(:NETASSETS???????? ,'0')",??
ANNUALINCOME???????? "nvl(:ANNUALINCOME????? ,'0')",??
SCOPE??????????????? "nvl(:SCOPE???????????? ,'0')",??
LIMIT??????????????? "nvl(:LIMIT???????????? ,'0')",??
CREDITDATE?????????? "nvl(:CREDITDATE??????? ,'0')",??
LICENSENO??????????? "nvl(:LICENSENO???????? ,'0')",??
LICENSEDATE????????? "nvl(:LICENSEDATE?????? ,'0')",??
LICENSEMATURITY????? "nvl(:LICENSEMATURITY?? ,'0')",??
SETUPDATE??????????? "nvl(:SETUPDATE???????? ,'0')",??
INSPECTIONYEAR?????? "nvl(:INSPECTIONYEAR??? ,'0')",??
LOCKSITUATION??????? "nvl(:LOCKSITUATION???? ,'0')",??
TAXNO??????????????? "nvl(:TAXNO???????????? ,'0')",??
BANKLICENSE????????? "nvl(:BANKLICENSE?????? ,'0')",??
BANKID?????????????? "nvl(:BANKID??????????? ,'0')",??
MANAGEAREA?????????? "nvl(:MANAGEAREA??????? ,'0')",??
BANCHAMOUNT????????? "nvl(:BANCHAMOUNT?????? ,'0')",??
EXCHANGEID?????????? "nvl(:EXCHANGEID??????? ,'0')",??
REGISTERADD????????? "nvl(:REGISTERADD?????? ,'0')",??
CHARGEDEPARTMENT???? "nvl(:CHARGEDEPARTMENT ,'0')",??
OFFICEADD??????????? "nvl(:OFFICEADD???????? ,'0')",??
OFFICEZIP??????????? "nvl(:OFFICEZIP???????? ,'0')",??
COUNTRYCODE????????? "nvl(:COUNTRYCODE?????? ,'0')",??
REGIONCODE?????????? "nvl(:REGIONCODE??????? ,'0')",??
VILLAGECODE????????? "nvl(:VILLAGECODE?????? ,'0')",??
VILLAGENAME????????? "nvl(:VILLAGENAME?????? ,'0')",??
RELATIVETYPE???????? "nvl(:RELATIVETYPE????? ,'0')",??
OFFICETEL??????????? "nvl(:OFFICETEL???????? ,'0')",??
OFFICEFAX??????????? "nvl(:OFFICEFAX???????? ,'0')",??
WEBADD?????????????? "nvl(:WEBADD??????????? ,'0')",??
EMAILADD???????????? "nvl(:EMAILADD????????? ,'0')",??
EMPLOYEENUMBER?????? "nvl(:EMPLOYEENUMBER??? ,'0')",??
MAINPRODUCTION?????? "nvl(:MAINPRODUCTION??? ,'0')",??
NEWTECHCORPORNOT???? "nvl(:NEWTECHCORPORNOT ,'0')",??
LISTINGCORPORNOT???? "nvl(:LISTINGCORPORNOT ,'0')",??
HASIERIGHT?????????? "nvl(:HASIERIGHT??????? ,'0')",??
HASDIRECTORATE?????? "nvl(:HASDIRECTORATE??? ,'0')",??
BASICBANK??????????? "nvl(:BASICBANK???????? ,'0')",??
BASICACCOUNT???????? "nvl(:BASICACCOUNT????? ,'0')",??
MANAGEINFO?????????? "nvl(:MANAGEINFO??????? ,'0')",??
CUSTOMERHISTORY????? "nvl(:CUSTOMERHISTORY?? ,'0')",??
PROJECTFLAG????????? "nvl(:PROJECTFLAG?????? ,'0')",??
REALTYFLAG?????????? "nvl(:REALTYFLAG??????? ,'0')",??
WORKFIELDAREA??????? "nvl(:WORKFIELDAREA???? ,'0')",??
WORKFIELDFEE???????? "nvl(:WORKFIELDFEE????? ,'0')",??
ACCOUNTDATE????????? "nvl(:ACCOUNTDATE?????? ,'0')",??
LOANCARDNO?????????? "nvl(:LOANCARDNO??????? ,'0')",??
LOANCARDPASSWORD???? "nvl(:LOANCARDPASSWORD ,'0')",??
LOANCARDINSYEAR????? "nvl(:LOANCARDINSYEAR?? ,'0')",??
LOANCARDINSRESULT??? "nvl(:LOANCARDINSRESULT ,'0')",??
LOANFLAG???????????? "nvl(:LOANFLAG????????? ,'0')",??
FINANCEORNOT???????? "nvl(:FINANCEORNOT????? ,'0')",??
FINANCEBELONG??????? "nvl(:FINANCEBELONG???? ,'0')",??
CREDITBELONG???????? "nvl(:CREDITBELONG????? ,'0')",??
CREDITLEVEL????????? "nvl(:CREDITLEVEL?????? ,'0')",??
EVALUATEDATE???????? "nvl(:EVALUATEDATE????? ,'0')",??
OTHERCREDITLEVEL???? "nvl(:OTHERCREDITLEVEL ,'0')",??
OTHEREVALUATEDATE??? "nvl(:OTHEREVALUATEDATE ,'0')",??
OTHERORGNAME???????? "nvl(:OTHERORGNAME????? ,'0')",??
INPUTORGID?????????? "nvl(:INPUTORGID??????? ,'0')",??
INPUTUSERID????????? "nvl(:INPUTUSERID?????? ,'0')",??
INPUTDATE??????????? "nvl(:INPUTDATE???????? ,'0')",??
UPDATEORGID????????? "nvl(:UPDATEORGID?????? ,'0')",??
UPDATEUSERID???????? "nvl(:UPDATEUSERID????? ,'0')",??
UPDATEDATE?????????? "nvl(:UPDATEDATE??????? ,'0')",??
REMARK?????????????? "nvl(:REMARK??????????? ,'0')",??
TAXNO1?????????????? "nvl(:TAXNO1??????????? ,'0')",??
FICTITIOUSPERSONID?? "nvl(:FICTITIOUSPERSONID,'0')",??
GROUPFLAG??????????? "nvl(:GROUPFLAG???????? ,'0')",??
EVALUATELEVEL??????? "nvl(:EVALUATELEVEL???? ,'0')",??
MYBANK?????????????? "nvl(:MYBANK??????????? ,'0')",??
MYBANKACCOUNT??????? "nvl(:MYBANKACCOUNT???? ,'0')",??
OTHERBANK??????????? "nvl(:OTHERBANK???????? ,'0')",??
OTHERBANKACCOUNT???? "nvl(:OTHERBANKACCOUNT ,'0')",??
TEMPSAVEFLAG???????? "nvl(:TEMPSAVEFLAG????? ,'0')",??
FINANCEDEPTTEL?????? "nvl(:FINANCEDEPTTEL??? ,'0')",??
ECGROUPFLAG????????? "nvl(:ECGROUPFLAG?????? ,'0')",??
SUPERCORPNAME??????? "nvl(:SUPERCORPNAME???? ,'0')",??
SUPERLOANCARDNO????? "nvl(:SUPERLOANCARDNO?? ,'0')",??
SUPERCERTTYPE??????? "nvl(:SUPERCERTTYPE???? ,'0')",??
SMEINDUSTRYTYPE????? "nvl(:SMEINDUSTRYTYPE?? ,'0')",??
SELLSUM????????????? "nvl(:SELLSUM?????????? ,'0')",??
SUPERCERTID????????? "nvl(:SUPERCERTID?????? ,'0')",??
ISVOUCHCORP????????? "nvl(:ISVOUCHCORP?????? ,'0')"
)REPORT_CATALOG.ctl：==
LOAD DATA
INFILE '/oracle/cfain/REPORT_CATALOG_$DATE.txt'
TRUNCATE
INTO TABLE AIX_REPORT_CATALOG
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
MODELNO "NVL(:MODELNO,'0')",
MODELNAME "NVL(:MODELNAME,' ')",
MODELTYPE "NVL(:MODELTYPE,'0')",
MODELCLASS "NVL(:MODELCLASS,'0')"
)REPORT_DATA.ctl：==
LOAD DATA
INFILE '/oracle/cfain/REPORT_DATA_$DATE.txt'
TRUNCATE
INTO TABLE AIX_REPORT_DATA
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
REPORTNO "NVL(:REPORTNO,'0')",
ROWNO "NVL(:ROWNO,' ')",
ROWNAME "NVL(:ROWNAME,'0')",
COL2VALUE "NVL(:COL2VALUE,'0')"
)REPORT_RECORD.ctl：==
LOAD DATA
INFILE '/oracle/cfain/REPORT_RECORD_$DATE.txt'
TRUNCATE
INTO TABLE AIX_REPORT_RECORD
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
REPORTNO "NVL(:REPORTNO,'0')",
REPORTDATE "NVL(:REPORTDATE,' ')",
MODELNO "NVL(:MODELNO,'0')",
REPORTSCOPE "NVL(:REPORTSCOPE,'0')",
OBJECTNO "NVL(:OBJECTNO,'0')"
)那一天的数据文件名举例：
ENT_INFO_20090909.txt
REPORT_CATALOG_20090909.txt
REPORT_DATA_20090909.txt
REPORT_RECORD_20090909.txt6、用ftp配置Unix/Linux文件的自动传输
ftpget.sh文件内容：（RedHat AS4环境下）
export DATE=`date +%Y%m%d`;
ftp -n<<!
open 192.168.80.5
user oracle oracle
prompt off
mget dl_$DATE.dmp yh_$DATE.dmp
quit
!
注：顺带说明一下：linux下,ftp的相关启动关闭命令如下:
[root@localhost ~]# /etc/init.d/vsftpd start
[root@localhost ~]# /etc/init.d/vsftpd stop
[root@localhost ~]# /etc/init.d/vsftpd restart
说明：.sh脚本要有执行权限;ftp服务开启与关闭要在根用户下执行;本文用到的例子有在RedHat as4环境下的,有在IBM AIX环境下的，其中涉及到的环境变量等，都大同小异;脚本测试好之后可以配在Linux/Unix调度程序crontab中，以实现shell的自动定时运行！
