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.违反唯一键或约束的更新运算会导致失败回滚。