Unix/Linux之oracle相关shell
上一篇 / 下一篇 2009-10-09 10:44:29 / 个人分类:oracle
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/impcfa/cfa@orclfile=/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.txt 6、用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的自动定时运行! |
TAG:
标题搜索
日历
|
|||||||||
日 | 一 | 二 | 三 | 四 | 五 | 六 | |||
1 | 2 | 3 | 4 | 5 | 6 | ||||
7 | 8 | 9 | 10 | 11 | 12 | 13 | |||
14 | 15 | 16 | 17 | 18 | 19 | 20 | |||
21 | 22 | 23 | 24 | 25 | 26 | 27 | |||
28 | 29 | 30 |
我的存档
数据统计
- 访问量: 29729
- 日志数: 27
- 图片数: 1
- 书签数: 2
- 建立时间: 2008-07-16
- 更新时间: 2015-03-11