2012-01-09 log errors into clause

新知识点1: log errors into clause

scott@ORCL-10.1.16.14>exec dbms_errlog.create_error_log('emp','emp_log');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
scott@ORCL-10.1.16.14>insert into emp select * from emp where rownum<2 log errors into emp_log reject limit unlimited;

0 rows created.

Elapsed: 00:00:00.03
scott@ORCL-10.1.16.14>exec print_table('select * from emp_log');
ORA_ERR_NUMBER$               : 1
ORA_ERR_MESG$                 : ORA-00001: unique constraint (SCOTT.BIN$SI2t9kIDSIqDEsAbIOvAWg==$2) violated

ORA_ERR_ROWID$                :
ORA_ERR_OPTYP$                : I
ORA_ERR_TAG$                  :
EMPNO                         : 7369
ENAME                         : SMITH
JOB                           : CLERK
MGR                           : 7902
HIREDATE                      : 17-DEC-80
SAL                           : 800
COMM                          :
DEPTNO                        : 20
-----------------

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01


关于有说加上append 提示后error log 无效，实验结果却不然：
scott@ORCL-10.1.16.14>truncate table emp_log;

Table truncated.

Elapsed: 00:00:00.01
scott@ORCL-10.1.16.14>insert /*+append*/into emp select * from emp where rownum<2 log errors into emp_log reject limit unlimited;

0 rows created.

Elapsed: 00:00:00.01
scott@ORCL-10.1.16.14>exec print_table('select * from emp_log');
ORA_ERR_NUMBER$               : 1
ORA_ERR_MESG$                 : ORA-00001: unique constraint (SCOTT.BIN$SI2t9kIDSIqDEsAbIOvAWg==$2) violated

ORA_ERR_ROWID$                :
ORA_ERR_OPTYP$                : I
ORA_ERR_TAG$                  :
EMPNO                         : 7369
ENAME                         : SMITH
JOB                           : CLERK
MGR                           : 7902
HIREDATE                      : 17-DEC-80
SAL                           : 800
COMM                          :
DEPTNO                        : 20
-----------------

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

数据库版本：
scott@ORCL-10.1.16.14>select * from v$version where rownum<2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

1 row selected.

Elapsed: 00:00:00.01
可能10G的结果不一样

--update on oct, 9th, 2012

nat@ORCL-172.17.103.108>insert /*+append*/ into emp select * from emp where rownum<14 log errors into emp_log reject limit unlimited;

0 rows created.

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 1973284518

---------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |      |    13 |   494 |     3   (0)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL | EMP  |       |       |            |          |
|*  2 |   COUNT STOPKEY          |      |       |       |            |          |
|   3 |    TABLE ACCESS FULL     | EMP  |    14 |   532 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROWNUM<14)


Statistics
----------------------------------------------------------
        196  recursive calls
        200  db block gets
        108  consistent gets
          0  physical reads
      18968  redo size
        918  bytes sent via SQL*Net to client
       1073  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          0  rows processed

由此看出这里的append提示失效了，才将错误记录下来
所以说加append提示会使log error 无效并不严谨，文档上的说明更准确：
– Any direct-path INSERT or MERGE operation that raises a unique constraint or
index violation.

--update on oct, 9th, 2012


reject limit 的含义：
scott@ORCL-10.1.16.14>insert into emp select * from emp where rownum<10 log errors into emp_log reject limit 9;

0 rows created.

Elapsed: 00:00:00.00
scott@ORCL-10.1.16.14>insert into emp select * from emp where rownum<10 log errors into emp_log reject limit 8;
insert into emp select * from emp where rownum<10 log errors into emp_log reject limit 8
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.BIN$SI2t9kIDSIqDEsAbIOvAWg==$2) violated


Elapsed: 00:00:00.00
 
--update on oct, 9th, 2012
一些讨论：
http://www.itpub.net/thread-1726645-1-1.html

Restrictions on DML Error Logging
■ The following conditions cause the statement to fail and roll back without
invoking the error logging capability:
– Violated deferred constraints.
– Any direct-path INSERT or MERGE operation that raises a unique constraint or
index violation.
– Any update operation UPDATE or MERGE that raises a unique constraint or
index violation).
■ You cannot track errors in the error logging table for LONG, LOB, or object type
columns. However, the table that is the target of the DML operation can contain
these types of columns.
– If you create or modify the corresponding error logging table so that it
contains a column of an unsupported type, and if the name of that column
corresponds to an unsupported column in the target DML table, then the DML
statement fails at parse time.
– If the error logging table does not contain any unsupported column types,
then all DML errors are logged until the reject limit of errors is reached. For
rows on which errors occur, column values with corresponding columns in the
error logging table are logged along with the control information.

对于这句话：
The following conditions cause the statement to fail and roll back without
invoking the error logging capability:
– Any update operation UPDATE or MERGE that raises a unique constraint or
index violation).
测试结果的确与文档描述不符，再看这个测试,只有2条记录的情况：
SELECT rowid,t.* FROM dmltab t;

ROWID                      ID
------------------ ----------
AAAgfoAAEAAACgUAAB          2
AAAgfoAAEAAACgUAAA          3

update报错
update dmltab set id = 1  WHERE ID <>1 log errors into err$_dmltab reject limit unlimited;

update dmltab set id = 1  WHERE ID <>1 log errors into err$_dmltab reject limit unlimited
*
ERROR at line 1:
ORA-00001: unique constraint (*****.SYS_C0067770) violated

没有记录错误信息
select * from  err$_dmltab;

no rows selected

the statement to fail and roll back 是没问题的
without invoking the error logging capability 在一个update里对同一个值违反N次唯一约束却记录了N-1条错误信息....

9条记录
SELECT rowid,t.* FROM dmltab t;

ROWID                      ID
------------------ ----------
AAAgfoAAEAAACgUAAD          0
AAAgfoAAEAAACgUAAC          1
AAAgfoAAEAAACgUAAB          2
AAAgfoAAEAAACgUAAA          3
AAAgfoAAEAAACgUAAE          4
AAAgfoAAEAAACgUAAF          5
AAAgfoAAEAAACgUAAG          6
AAAgfoAAEAAACgUAAH          7
AAAgfoAAEAAACgUAAI          8

9 rows selected.

SELECT id,mod(ID,2) FROM dmltab  WHERE mod(ID,3) <>0;

        ID  MOD(ID,2)
---------- ----------
         1          1
         2          0
         4          0
         5          1
         7          1
         8          0

这个update 对于值0违反唯一约束3次,值1违反2次
update dmltab set id =mod(ID,2)  WHERE mod(ID,3) <>0 log errors into err$_dmltab reject limit unlimited;
update dmltab set id =mod(ID,2)  WHERE mod(ID,3) <>0 log errors into err$_dmltab reject limit unlimited
*
ERROR at line 1:
ORA-00001: unique constraint (******.SYS_C0067770) violated



记录错误0，2条，1，一条
select id from  err$_dmltab;

ID
----------------------
0
1
0

3 rows selected.


--update on oct, 9th, 2012

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24383181/viewspace-714627/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24383181/viewspace-714627/