Oracle & Starcraft

10gr2的logerror功能有点接近于以前的exception表，不过相比exception表它更灵活，内容更丰富。

SQL 10G>desc test
Name Null? Type
—————————————– ——– —————————-
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)

首先我们的创建error表

SQL 10G>exec dbms_errlog.create_error_log(’TEST’ );

PL/SQL procedure successfully completed.

创建的error表以err$_开头，加上表名，包含了test表的所有列

SQL 10G>desc err$_test
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)
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)

让我们测试一下log error功能，首先创建一个pk

SQL 10G>alter table test add constraint test_pk primary key(object_id);

Table altered.

插入重复的数据，这时候不会报错，但是插入的记录数是0

SQL 10G>insert into test select * from test where rownum<2
2 LOG ERRORS REJECT LIMIT UNLIMITED;

0 rows created.

再看error表的内容，记下来一条，dml type是I也就是Insert，error number是1

SQL 10G>select * from err$_test;
1
ORA-00001: unique constraint (TEST.TEST_PK) violated

I

SYS
ICOL$

20
2
TABLE
30-JUN-05
30-JUN-05
2005-06-30:19:10:16
VALID
N
N
N

对刚才的dml做rollback，发现并不影响error表的内容

SQL 10G>rollback;

Rollback complete.

SQL 10G>select * from err$_test;
1
ORA-00001: unique constraint (TEST.TEST_PK) violated

I

SYS
ICOL$

20
2
TABLE
30-JUN-05
30-JUN-05
2005-06-30:19:10:16
VALID
N
N
N

truncate也不影响error表的内容

SQL 10G>truncate table test;

Table truncated.

SQL 10G>select * from err$_test;
1
ORA-00001: unique constraint (TEST.TEST_PK) violated

I

SYS
ICOL$

20
2
TABLE
30-JUN-05
30-JUN-05
2005-06-30:19:10:16
VALID
N
N
N

drop table同样对error表没有影响

SQL 10G>drop table test;

Table dropped.

SQL 10G>select * from err$_test;
1
ORA-00001: unique constraint (TEST.TEST_PK) violated

I

SYS
ICOL$

20
2
TABLE
30-JUN-05
30-JUN-05
2005-06-30:19:10:16
VALID
N
N
N

log error在这里有一个问题，如果删除test表后重建一张结构不一样的test表，那么error表将不会包含新test表的列
，有可能出现混乱。

SQL 10G>create table test(a number);

Table created.

SQL 10G>alter table test add constraint test_pk primary key(a);

Table altered.

SQL 10G>insert into test values(1);

1 row created.

SQL 10G>insert into test values(1) LOG ERRORS REJECT LIMIT UNLIMITED;

0 rows created.

SQL 10G>commit;

Commit complete.

SQL 10G>select * from err$_test;
1
ORA-00001: unique constraint (TEST.TEST_PK) violated

I

SYS
ICOL$

20
2
TABLE
30-JUN-05
30-JUN-05
2005-06-30:19:10:16
VALID
N
N
N

1
ORA-00001: unique constraint (TEST.TEST_PK) violated

I

可以看到上面的第2条记录是新的test表的异常记录，不包含列信息。

下面来看一下log error最多被使用的情况，通常我们在大批量的dml时如果其中一条
记录不符合条件那么整个事务将会被回滚，所以我们有时候不得不使用循环来实现，
但是使用循环会增加undo和redo，并且我们需要记录当前事务执行到哪一条记录避免
失败后从头开始，有了log error功能，我们可以使用一句语句来完成。

SQL 10G>truncate table err$_test;

Table truncated.

SQL 10G>drop table test;

Table dropped.

SQL 10G>create table test as select * from dba_objects where 1=0;

Table created.

SQL 10G>alter table test add constraint test_pk primary key(object_id);

Table altered.

SQL 10G>insert into test select * from dba_objects;
insert into test select * from dba_objects
*
ERROR at line 1:
ORA-01400: cannot insert NULL into (”TEST”.”TEST”.”OBJECT_ID”)

SQL 10G>select count(*) from test;

COUNT(*)
———-
0

SQL 10G>insert into test select * from dba_objects
2 LOG ERRORS REJECT LIMIT UNLIMITED;

50189 rows created.

SQL 10G>commit;

Commit complete.

SQL 10G>select count(*) from err$_test;

COUNT(*)
———-
1

SQL 10G>select * from err$_test;

1400
ORA-01400: cannot insert NULL into (”TEST”.”TEST”.”OBJECT_ID”)

I

DICTMGR
LNK_DBC.REGRESS.RDBMS.DEV.US.ORACLE.COM

DATABASE LINK
28-NOV-05

VALID
N
N
N

log error是不错的东西，大家在批量导数据的时候可以考虑一下哦。