insert into select 批量加载疏失解决方案--Java学习网

时间:2015-12-8 6:16:58

  核心提示:insert into select 批量加载出错解决方案当使用insert into select 批量加载数据的时候,可能会碰到因为某些数据不符合加载条件,而导致整个insert 语句无法执行,全...
insert into select 批量加载出错解决方案 

当使用insert into select 批量加载数据的时候,可能会碰到因为某些数据不符合加载条件,而导致整个insert 语句无法执行,全部rollback。这时可以使用DML 错误日志的特性,解决这个问题。

只需要创建一个日志表,并且在使用dml语句的时候添加dml error logging 语句,即可将错误的rows记录到日志表中,而且不会影响已经加载到表中的数据。最后修正这些无法加载的数据。

操作步骤如下:

1.     创建一个日志表

可以使用DBMS_ERRLOG包创建,或者手动的创建日志表。如果使用dbms_errlog.create_error_log来创建日志表,默认的会将源表的所有列都加入需要记录的行列中。

2.     执行insert ,并且添加error logging 语句。

3.     最后查询日志表,修改无法加载的数据。

 

以下是一个insert into select 批量加载的例子:

第一步创建日志表

 

创建测试表target_t

create table target_t (id number(4),namevarchar2(2000)) ;

创建源表source_t

create table source_t as select level as id ,'name' || level as name from dual connect by level <= 10004 ;

使用存储过程创建error logging table target_err_t

使用DBMS_ERRLOG.CREATE_ERROR_LOG会自动补全表中的所有列到日志表中。(这里表名竟然不区分大小写,看来OracleDabase在向好的方向发展了)

EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG(dml_table_name => 'target_t',err_log_table_name=> 'target_err_log') ;


 

查看一下表结构

dexter@ORCL> desc target_err_log ;
 Name                                                 Null?    Type
 ------------------------- ------------------------------------
 ORA_ERR_NUMBER$                                               NUMBER
 ORA_ERR_MESG$                                                  VARCHAR2(2000)
 ORA_ERR_ROWID$                                                ROWID
 ORA_ERR_OPTYP$                                                VARCHAR2(2)
 ORA_ERR_TAG$                                                   VARCHAR2(2000)
 ID                                                            VARCHAR2(4000)
 NAME                                                          VARCHAR2(4000)

第二步执行insert

这里多了error logging 语句LOG ERRORS INTO target_err_log ('daily_load')REJECT LIMIT 4其中target_err_log为error日志表名,'daily_load'对应日志表中的ORA_ERR_TAG$,便于区分每次的操作。reject limit 4 中的4表示最多可以遇到4次错误。但是error 日志表中最多会插入5条记录(因为只有碰到第五次错误才会终止嘛)。另外,insert 操作 与 日志表操作不在同一个事务,也就是说当前insert 操作的 commit 和 rollback 不会影响日志表中的记录

insert into target_t select * from source_t LOGERRORS INTO target_err_log ('daily_load') REJECT LIMIT 4 ;
                            *
ERROR at line 1:
ORA-01438: value larger than specifiedprecision allowed for this column
 
col ORA_ERR_NUMBER$ for 999999
col ORA_ERR_MESG$ for a40
col ORA_ERR_ROWID$ for a10
col ORA_ERR_OPTYP$ for a10
col ORA_ERR_TAG$ for a20
col id for a20
col name for a20
set lines 150
 
 
dexter@ORCL> select * from target_err_log ;
 
ORA_ERR_NUMBER$ ORA_ERR_MESG$                            ORA_ERR_ROORA_ERR_OP ORA_ERR_TAG$         ID                   NAME
------------------------------------------------------- ---------- ------------------------------ -------------------- --------------------
          1438 ORA-01438: value larger than specified p            I          daily_load           10000        name10000
               recision allowed for this column
 
          1438 ORA-01438: value larger than specified p            I          daily_load           10001        name10001
               recision allowed for this column
 
          1438 ORA-01438: value larger than specified p            I          daily_load           10002        name10002
               recision allowed for this column
 
          1438 ORA-01438: value larger than specified p            I          daily_load           10003        name10003
               recision allowed for this column
 
          1438 ORA-01438: value larger than specified p            I          daily_load           10004        name10004
               recision allowed for this column
 


一共5条无法加载的数据,完整记录了下来

 

 

insert into target_t select * from source_t LOGERRORS INTO target_err_log ('daily_load2') REJECT LIMIT 5;
 
9999 rows created.
 
 
dexter@ORCL> select count(*) from target_t ;
 
 COUNT(*)
----------
     9999
支持rollback
dexter@ORCL> rollback ;
 
Rollback complete.
 
dexter@ORCL> select count(*) from target_t ;
 
 COUNT(*)
----------
        0
 
dexter@ORCL> insert into target_t select *from source_t LOG ERRORS INTO target_err_log ('daily_load2') REJECT LIMIT 5;
 
9999 rows created.
 
dexter@ORCL> commit ;
 
Commit complete.
 
dexter@ORCL> select count(*) from target_t ;
 
 COUNT(*)
----------
     9999
dexter@ORCL> select count(*) from target_t ;
 
 COUNT(*)
----------
         15


 

第三步修改数据

这里我就不多说。

 

 

dml error logging 还支持UPDATE, MERGE,DELETE语句。

附录

日志表中各字段解读

 


 

 

Column Name                                    Data Type                                    Description

ORA_ERR_NUMBER$                       NUMBER                                    Oracle error number

ORA_ERR_MESG$                            VARCHAR2(2000)                        Oracle errormessage text

ORA_ERR_ROWID$                           ROWID                                        Rowid of the row inerror (for update and delete)

ORA_ERR_OPTYP$                           VARCHAR2(2)                              Type ofoperation: insert (I), update (U), delete (D)

                                                                                                                 Note: Errors from the update clause and

                                                                                                                 insert clause of a MERGEoperation are

                                                                                                                 distinguished by the Uand Ivalues.

ORA_ERR_TAG$                                 VARCHAR2(2000)                         Value of thetag supplied by the user in the error logging clause

Java免费学习   Java自学网 http://www.javalearns.com

关注微信号:javalearns   随时随地学Java

或扫一扫

随时随地学Java

作者:不详 来源:网络
    你是从哪里知道本网站的?
  • 网友介绍的
  • 百度搜索的
  • Google搜索的
  • 其它搜索过来的
  • 网址输错了进来的
  • 太忙了不记得了
共有评论 0相关评论
发表我的评论
  • 大名:
  • 内容:
  • java学习网(www.javalearns.com) © 2014 版权所有 All Rights Reserved.
  • Email:javalearns@163.com 站长QQ:1356121699 晋ICP备14003680号-3
  • java学习网部分内容来自网络或网友发布,如侵犯了您利益,请发邮件至:javalearns@126.com,我们尽快处理!
  • Java学习网
  • 网站统计
  • 晋公网安备 14042902000001号