sqlloader 导入数据

1、提取日志,过滤出sqlldr的日志导入本地，（多个sqlldr可以先合并成一个文件），再替换掉多余的字符串，验证去重再导出sql2、上传压缩好的sqllr数据日志文件到服务器3、解压上传的sqllr数据压缩包unzip sqlldrlog202110251554.zip4、执行bash环境变量#!/bin/bashPATH=$PATH:$HOME/.local/bin:$HOME/binexp

weixin_44090237

3619人浏览 · 2021-11-29 09:55:39

weixin_44090237 · 2021-11-29 09:55:39 发布

1、提取日志,过滤出sqlldr的日志导入本地，（多个sqlldr可以先合并成一个文件），再替换掉多余的字符串，验证去重再导出sql
2、上传压缩好的sqllr数据日志文件到服务器
3、解压上传的sqllr数据压缩包

unzip sqlldrlog202110251554.zip

4、执行bash环境变量

#!/bin/bash
PATH=$PATH:$HOME/.local/bin:$HOME/bin
export PATH
ORACLE_BASE=/u01/app/oracle
export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/client
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH

umask 022
export NLS_LANG=american_america.ZHS16GBK
export NLS_DATE_FORMAT='YYYY-MM-DD'

LC_CTYPE=zh_CN.GB2312
LC_ALL=zh_CN
LANG=zh_CN.GB2312

5、执行sqlldr导入命令

sqlldr userid=xubaouser/'Xubao!230Yonghu'@9.23.2.213:1521/nxubaodb control=insertIntoTD_CORE_PUSH_DATA_TEST1.ctl data=sqlldrlog202110251554.txt log=sqlldrlog202110251554.log bad=sqlldrlog202110251554.bad errors=99999999

控制文件

LOAD DATA
APPEND INTO TABLE TD_CORE_PUSH_DATA_TEST1
FIELDS TERMINATED BY ‘||’
optionally enclosed by ‘"’
TRAILING NULLCOLS (
VINNO CHAR,
LICENSENO CHAR, PROVISONALNO CHAR, PROPOSALNO CHAR, POLICYNO CHAR,
SYSTEMSOURCE CHAR, CHANNELTYPE CHAR, BUSINESSNATURE CHAR, COMCODE
CHAR, HANDLER1CODE CHAR, HANDLER1NAME CHAR, HANDLERCODE CHAR,
HANDLERNAME CHAR, LASTENDDATE CHAR, RENEWALFLAG CHAR, OPTTYPE CHAR,
OPTDATE SYSDATE, “CONTENT” CHAR, FLAG CHAR, REMARK CHAR, STARTDATE
“TO_DATE(:STARTDATE,‘YYYY-MM-DD’)”, ENDDATE
“TO_DATE(:ENDDATE,‘YYYY-MM-DD’)”, RISKCODE CHAR, PUSHNO
“SEQ_TD_CORE_PUSH_DATA.NEXTVAL” )