当你插入几百万数据时，因为有几条脏数据而导致插入失败，是不是非常恼火。oracle 10g r2之后有个新功能，将插入过程中失败的记录
当你插入几百万数据时，因为有几条脏数据而导致插入失败，是不是非常恼火。oracle 10g r2之后有个新功能，，将插入过程中失败的记录插入到另一张表中。
sql> drop table test purge;
sql> drop table test_bad purge;
sql> create table test as select * from dba_objects where 11;
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 11;
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) 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) count(1)
----------
106
需要说明的是：
1.插入到错误日志是自治事务，不会影响主事务。
2.使用log error并不会使append失效，但写error不会使用直接路径插入。
3.违反唯一键或约束的更新运算会导致失败回滚。
oracle dml流程
pl/sql“ ora-14551: 无法在查询中执行 dml 操作”解决
mysql常用ddl、dml、dcl语言整理（附样例）
oracle基本事务和forall执行批量dml练习
oracle dml语句(insert,update,delete) 回滚开销估算
本文永久更新链接地址：