DML过程中记录错误日志

当你插入几百万数据时，因为有几条脏数据而导致插入失败，是不是非常恼火。Oracle 10g R2之后有个新功能，将插入过程中失败的记录插入到另一张表中。SQL> drop table test purge;SQL> drop table test_bad purge;
SQL> create table test as select * from dba_objects where 1<>1;SQL> execute dbms_errlog.create_error_log（"test","test_bad"）;create table TEST
（
OWNER VARCHAR2（30）,
OBJECT_NAME VARCHAR2（128）,
SUBOBJECT_NAME VARCHAR2（30）,
OBJECT_ID NUMBER,
DATA_OBJECT_ID NUMBER,
OBJECT_TYPE VARCHAR2（19）,
CREATED DATE,
LAST_DDL_TIME DATE,
TIMESTAMP VARCHAR2（19）,
STATUS VARCHAR2（7）,
TEMPORARY VARCHAR2（1）,
GENERATED VARCHAR2（1）,
SECONDARY VARCHAR2（1）,
NAMESPACE NUMBER,
EDITION_NAME VARCHAR2（30）
）;
create table TEST_BAD
（
ORA_ERR_NUMBER$ NUMBER,
ORA_ERR_MESG$ VARCHAR2（2000）,
ORA_ERR_ROWID$ UROWID（4000）,
ORA_ERR_OPTYP$ VARCHAR2（2）,
ORA_ERR_TAG$ VARCHAR2（2000）,
OWNER VARCHAR2（4000）,
OBJECT_NAME VARCHAR2（4000）,
SUBOBJECT_NAME VARCHAR2（4000）,
OBJECT_ID VARCHAR2（4000）,
DATA_OBJECT_ID VARCHAR2（4000）,
OBJECT_TYPE VARCHAR2（4000）,
CREATED VARCHAR2（4000）,
LAST_DDL_TIME VARCHAR2（4000）,
TIMESTAMP VARCHAR2（4000）,
STATUS VARCHAR2（4000）,
TEMPORARY VARCHAR2（4000）,
GENERATED VARCHAR2（4000）,
SECONDARY VARCHAR2（4000）,
NAMESPACE VARCHAR2（4000）,
EDITION_NAME VARCHAR2（4000）
）;
SQL> insert into test（owner） values（lpad（"1",31,"aa"））
log errors into test_bad;
insert into test（owner） values（lpad（"1",31,"aa"））
*
第 1 行出现错误:
ORA-12899: 列 "LCAM_TEST"."TEST"."OWNER" 的值太大（实际值: 31, 最大值: 30）
SQL> insert into test（owner） values（lpad（"2",30,"bb"））
log errors into test_bad;
已创建 1 行。
SQL> col ORA_ERR_NUMBER format a8;
SQL> col ORA_ERR_MESG$ format a50;
SQL> col OWNER format a20;
SQL> select to_char（ORA_ERR_NUMBER$） as ORA_ERR_NUMBER, ORA_ERR_MESG$, OWNER from test_bad;
ORA_ERR_ ORA_ERR_MESG$ OWNER
-------- -------------------------------------------------- --------------------
12899 ORA-12899: 列 "LCAM_TEST"."TEST"."OWNER" 的值太大 aaaaaaaaaaaaaaaaaaaa
（实际值: 31, 最大值: 30） aaaaaaaaaa1
SQL> select count（1） from test;
COUNT（1）
----------
1

SQL> drop table test1 purge;
SQL> drop table test_bad1 purge;
SQL> create table test1 as select * from dba_objects where 1<>1;
SQL> alter table test1 modify object_id number（2）;
SQL> execute dbms_errlog.create_error_log（"test1","test_bad1"）;
SQL> insert into test1 select * from dba_objects
log errors into test_bad1;
insert into test1 select * from dba_objects
*
第 1 行出现错误:
ORA-01438: 值大于为此列指定的允许精度
SQL> select count（1） from test1;
COUNT（1）
----------
0
SQL> select count（1） from dba_objects where length（object_id）<=2 or object_id is null;
COUNT（1）
---------- 106--可以看到，上面的语句一有错误就回滚，需要写出下列语句SQL> insert into test1 select * from dba_objects
log errors into test_bad1
reject limit unlimited;
已创建106行。
SQL> select count（1） from test1;
COUNT（1）
----------
106
SQL> select count（1） from dba_objects where length（object_id）<=2 or object_id is null;
COUNT（1）
---------- 106需要说明的是：1.插入到错误日志是自治事务，不会影响主事务。
2.使用log error并不会使append失效，但写error不会使用直接路径插入。
3.违反唯一键或约束的更新运算会导致失败回滚。Oracle DML流程 http://www.linuxidc.com/Linux/2012-02/52972.htmPL/SQL“ ORA-14551: 无法在查询中执行 DML 操作”解决 http://www.linuxidc.com/Linux/2013-06/86713.htmMySQL常用DDL、DML、DCL语言整理（附样例） http://www.linuxidc.com/Linux/2013-06/86431.htmOracle基本事务和ForAll执行批量DML练习 http://www.linuxidc.com/Linux/2011-08/39836.htmOracle DML语句（insert,update,delete）回滚开销估算 http://www.linuxidc.com/Linux/2011-09/43287.htm更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址