oracle sqlloader导入时报ORA-01830解决过程

目标： 将/home/oracle/xfsqlldr.csv导入到新建的CHOICE.T_LOAN_ANNUAL_CUSTNO表中。 新建表： create table CHOICE.T_LOAN_ANNUAL_CUSTNO( LOANNO VARCHAR2(200)  , FUNDCUSTNO VARCHAR2(200)  , DATEDATE DATE  , DATETYPE VARCHAR2(2)  , IS_DEL  VARCHAR2(1) DEFAULT '0' not null, OITIME DATE DEFAULT sysdate not null, OUTIME DATE DEFAULT sysdate not null); 处理文本，将标题行去掉 sed -i '1d' xfsqlldr.csv more xfsqlldr.csv |wc -l 37291 编辑sqlloader控制文件 vi /home/oracle/xfsqlldr.ctl load data infile '/home/oracle/xfsqlldr.csv' insert into table choice.T_LOAN_ANNUAL_CUSTNO fields terminated by ',' OPTIONALLY ENCLOSED BY '"' (         LOANNO,     FUNDCUSTNO,     DATEDATE,     DATETYPE ) sqlloader导入： sqlldr "'/as sysdba'" ROWS=10000 control=/home/oracle/xfsqlldr.ctl LOG=/home/oracle/xfsqlldr.log    报错： value used for ROWS parameter changed from 10000 to 248 Record 1: Rejected - Error on table CHOICE.T_LOAN_ANNUAL_CUSTNO, column DATEDATE. ORA-01830: date format picture ends before converting entire input string Record 2: Rejected - Error on table CHOICE.T_LOAN_ANNUAL_CUSTNO, column DATEDATE. ORA-01830: date format picture ends before converting entire input string Record 3: Rejected - Error on table CHOICE.T_LOAN_ANNUAL_CUSTNO, column DATEDATE. ORA-01830: date format picture ends before converting entire input string Record 4: Rejected - Error on table CHOICE.T_LOAN_ANNUAL_CUSTNO, column DATEDATE. ORA-01830: date format picture ends before converting entire input string Record 5: Rejected - Error on table CHOICE.T_LOAN_ANNUAL_CUSTNO, column DATEDATE. ORA-01830: date format picture ends before converting entire input string 导入的时间列是如下格式： 2017/9/1 16:31:25.202000 秒后面的202000不能识别。 修改导入控制文件： load data infile '/home/oracle/xfsqlldr.csv' insert into table CHOICE.T_LOAN_ANNUAL_CUSTNO fields terminated by ',' OPTIONALLY ENCLOSED BY '"' (         LOANNO,     FUNDCUSTNO,     DATEDATE "to_date(substr(:DATEDATE,'0',instr(:DATEDATE,'.')-1),'yyyy-mm-dd hh24:mi:ss')",     DATETYPE ) select to_date(substr('2017/9/1 16:31:25.202000','0',instr('2017/9/1 16:31:25.202000','.')-1),'yyyy-mm-dd hh24:mi:ss') from dual; sqlloader再次导入： sqlldr "'/as sysdba'" ROWS=10000 control=/home/oracle/xfsqlldr.ctl LOG=/home/oracle/xfsqlldr.log    省略大部分输出： Commit point reached - logical record count 37044 Commit point reached - logical record count 37292 Commit point reached - logical record count 37540 Commit point reached - logical record count 37788 Commit point reached - logical record count 37921 检查日志： Table CHOICE.T_LOAN_ANNUAL_CUSTNO:   37921 Rows successfully loaded.   0 Rows not loaded due to data errors.   0 Rows not loaded because all WHEN clauses were failed.   0 Rows not loaded because all fields were null. Space allocated for bind array:                 255936 bytes(248 rows) Read   buffer bytes: 1048576 Total logical records skipped:          0 Total logical records read:         37921 Total logical records rejected:         0 Total logical records discarded:        0 检查数据： SQL> select count(*) from CHOICE.T_LOAN_ANNUAL_CUSTNO;   COUNT(*) ----------      37921 总行数对。 select count(LOANNO) from CHOICE.T_LOAN_ANNUAL_CUSTNO    ; COUNT(LOANNO) -------------         33466 sed -n '33465,33467p' xfsqlldr.csv 4106ed46b9de9370c001448a308881f1,4fdcf2ce524942b0a003757a615db4e9,2017/12/12 12:27:25.367000,2 a83c402de3c3233f7416884be2e2533c,290112dd17ca492184e13475f9b93817,2017/12/8 23:19:16.387000,2 ,1b78c7f7e04b46a19b0973fad39143f6,2018/5/15 21:13:38.867000,3 select count(FUNDCUSTNO) from CHOICE.T_LOAN_ANNUAL_CUSTNO    ;         COUNT(FUNDCUSTNO) -----------------             37921 该列没有空值，和总行数一致，正确。 select count(DATEDATE) from CHOICE.T_LOAN_ANNUAL_CUSTNO    ; COUNT(DATEDATE) ---------------           36453 sed -n '36452,36454p' xfsqlldr.csv ,8ea4d98b4825490c9c48a82307269175,2018/6/5 18:03:34.680643,3 ,2e4cc6b253f6434e9a0a010513256022,2018/4/9 11:51:54.507954,3 ,86f77fb6962943a78c00fd028bcdcaef,,4 正确。 SQL> select count(DATETYPE) from CHOICE.T_LOAN_ANNUAL_CUSTNO      ; COUNT(DATETYPE) ---------------           37921 该列没有空值，和总行数一致，正确。 赋权： SELECT ROLE FROM DBA_ROLES WHERE ROLE LIKE '%CHOICE%'; grant select on CHOICE.T_LOAN_ANNUAL_CUSTNO to R_CHOICE_READER; grant select on CHOICE.T_LOAN_ANNUAL_HISHOLD to R_CHOICE_READER; grant R_CHOICE_READER to p_chenzy_r,p_xufang_r; grant insert,update,delete on CHOICE.T_LOAN_ANNUAL_CUSTNO  to p_xufang_r; grant insert,update,delete on CHOICE.T_LOAN_ANNUAL_HISHOLD to p_xufang_r;