DML Error Logging 特性

最新推荐文章于 2023-02-26 22:23:10 发布

翻译最新推荐文章于 2023-02-26 22:23:10 发布 · 6.5k 阅读

文章标签：

#logging #insert #null #delete #merge

-----SQL/PLSQL基础同时被 2 个专栏收录

61 篇文章

订阅专栏

SQL PL/SQL基础系列

61 篇文章

订阅专栏

      最近的项目中发现处理DML Error 时，逐条逐条处理1千多条的数据从临时表 insert 到正式表需要差不多1分钟的时间，性能相当低下，而Oracle 10g中的DML error logging对于DML异常处理性能卓著。原本打算写篇关于这个特性的文章，正好有经典篇章，于是乎，索性翻译供大家参考，有不尽完美之处，请大家拍砖。
      缺省情况下，一个DML命令失败的时候，在侦测到错误之前，不论成功处理了多少条记录，都将将使得整个语句回滚。在使用DML error log之前，针对单行处理首选的办法是使用批量SQL FORALL 的SAVE EXCEPTIONS子句。而在Oracle 10g R2时，DML error log特性使得该问题得以解决。通过为大多数INSERT,UPDATE,MERGE,DELETE语句添加适当的LOG ERRORS子句，不论处理过程中是否出现错误，都可以使整个语句成功执行。这篇文章描述了DML ERROR LOGGING操作特性，并针对每一种情形给出示例。

一、语法
    对于INSERT, UPDATE, MERGE 以及 DELETE 语句都使用相同的语法
        LOG ERRORS [INTO [schema.]table] [('simple_expression')] [REJECT LIMIT integer|UNLIMITED]

    可选的INTO子句允许指定error logging table 的名字。如果省略它，则记录日志的表名的将以"ERR$_"前缀加上基表名来表示。

    simple_expression表达式可以用于指定一个标记，更方便去判断错误。simple_expression能够为一个字符串或任意能转换成字符串的函数

    REJECT LIMIT 通常用于判断当前语句所允许出现的最大错误数。缺省值是0，最大值则是使用UNLIMITED关键字。对于并行DML操作而言，REJECT LIMIT
        会应用到每个并行服务器。

二、使用限制
    下列情形使得DML error logging 特性失效
        延迟约束特性
        Direct-path INSERT 或MERGE 引起违反唯一约束或唯一索引
        UPDATE 或 MERGE 引起违反唯一约束或唯一索引

    除此之外，对于LONG,LOB,以及对象类型也不被支持。即使是一个包含这些列的表被作为错误日志记录目标表。

三、示例
    下面的代码创建表并填充数据用于演示。

-- Create and populate a source table.                                                        
                                                                                              
CREATE TABLE source                                                                           
(                                                                                             
   id                  NUMBER( 10 ) NOT NULL                                                  
  ,code                VARCHAR2( 10 )                                                         
  ,description         VARCHAR2( 50 )                                                         
  ,CONSTRAINT source_pk PRIMARY KEY( id )                                                     
);                                                                                            
                                                                                              
DECLARE                                                                                       
   TYPE t_tab IS TABLE OF source%ROWTYPE;                                                     
                                                                                              
   l_tab               t_tab := t_tab( );                                                     
BEGIN                                                                                         
   FOR i IN 1 .. 100000                                                                       
   LOOP                                                                                       
      l_tab.EXTEND;                                                                           
      l_tab( l_tab.LAST ).id            := i;                                                 
      l_tab( l_tab.LAST ).code          := TO_CHAR( i );                                      
      l_tab( l_tab.LAST ).description   := 'Description for ' || TO_CHAR( i );                
   END LOOP;                                                                                  
                                                                                              
   -- For a possible error condition.                                                         
   l_tab( 1000 ).code    := NULL;                                                             
   l_tab( 10000 ).code   := NULL;                                                             
                                                                                              
   FORALL i IN l_tab.FIRST .. l_tab.LAST                                                      
      INSERT INTO source                                                                      
      VALUES l_tab( i );                                                                      
                                                                                              
   COMMIT;                                                                                    
END;                                                                                          
/                                                                                             
                                                                                              
EXEC DBMS_STATS.gather_table_stats(USER, 'source', cascade => TRUE);                          
-- Create a destination table.                                                                
                                                                                              
CREATE TABLE dest                                                                             
(                                                                                             
   id                  NUMBER( 10 ) NOT NULL                                                  
  ,code                VARCHAR2( 10 ) NOT NULL                                                
  ,description         VARCHAR2( 50 )                                                         
  ,CONSTRAINT dest_pk PRIMARY KEY( id )                                                       
);                                                                                            
                                                                                              
-- Create a dependant of the destination table.                                               
                                                                                              
CREATE TABLE dest_child                                                                       
(                                                                                             
   id                  NUMBER                                                                 
  ,dest_id             NUMBER                                                                 
  ,CONSTRAINT child_pk PRIMARY KEY( id )                                                      
  ,CONSTRAINT dest_child_dest_fk FOREIGN KEY( dest_id ) REFERENCES dest( id )                 
);

    注意，code列在source 表中是可选，而在dest 表中是强制的

    一旦基表创建之后，如果需要使用DML error logging 特性，则必须为该基表创建一个日志表用于记录基于该表上的DML错误。错误日志表能够
    手动创建或者通过包中的CREATE_ERROR_LOG存储过程来创建。如下所示：

-- Create the error logging table.

BEGIN
   DBMS_ERRLOG.create_error_log( dml_table_name => 'dest' );
END;
/

pl/SQL procedure successfully completed.

--缺省情况下，创建的日志表基于当前schema。日志表的所有者以及日志名字，表空间名字也可以单独指定。缺省的日志表的名字基于基表并以
--"ERR$_"前缀开头。

SELECT owner, table_name, tablespace_name
FROM   all_tables
WHERE  owner = 'TEST';

OWNER                          TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
TEST                           DEST                           USERS
TEST                           DEST_CHILD                     USERS
TEST                           ERR$_DEST                      USERS
TEST                           SOURCE                         USERS

4 rows selected.

--日志表的结构以及数据类型和所允许的最大长度依赖于基表，如下所示：
SQL> DESC err$_dest
 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)
 ID                                         VARCHAR2(4000)
 CODE                                       VARCHAR2(4000)
 DESCRIPTION                                VARCHAR2(4000)

1、INSERT 操作
在前面创建演示表时，对于source表来说，其code 列可以为NULL，而dest表的code则不允许为NULL。在填充source表时，设置了两行为NULL的记录。
如果我们尝试从source 表复制数据到dest条，将获得下列错误信息

INSERT INTO dest
SELECT *
FROM   source;

SELECT *
       *
ERROR at line 2:
ORA-01400: cannot insert NULL into ("TEST"."DEST"."CODE")

--source 表为NULL的两行将引起整个insert 语句回滚，无论在错误之间有多少条语句被成功插入。通过添加DML error logging 子句，则允许我们
--对那些有效数据实现成功插入。

INSERT INTO dest
SELECT *
FROM   source
LOG ERRORS INTO err$_dest ('INSERT') REJECT LIMIT UNLIMITED;

99998 rows created.


--那些未能成功插入的记录将被记录在ERR$_DEST中，并且也记录了错误的原因。
COLUMN ora_err_mesg$ FORMAT A70
SELECT ora_err_number$, ora_err_mesg$
FROM   err$_dest
WHERE  ora_err_tag$ = 'INSERT';

ORA_ERR_NUMBER$ ORA_ERR_MESG$
--------------- ---------------------------------------------------------
           1400 ORA-01400: cannot insert NULL into ("TEST"."DEST"."CODE")
           1400 ORA-01400: cannot insert NULL into ("TEST"."DEST"."CODE")

2 rows selected.

2、UPDATE 操作
下面的代码将尝试去更新1-10行的code列，其中8行的code值设置为自身，而第9与第10行设置为NULL。

UPDATE dest
SET    code = DECODE(id, 9, NULL, 10, NULL, code)
WHERE  id BETWEEN 1 AND 10;
       *
ERROR at line 2:
ORA-01407: cannot update ("TEST"."DEST"."CODE") to NULL


--如我们所期待的那样，语句由于code列不允许为NULL而导致操作失败。同样，通过添加DML erorr logging子句允许我们完成有效记录的操作

UPDATE dest
SET    code = DECODE(id, 9, NULL, 10, NULL, code)
WHERE  id BETWEEN 1 AND 10
LOG ERRORS INTO err$_dest ('UPDATE') REJECT LIMIT UNLIMITED;

8 rows updated.

--同样地，update操作失败的行以及失败原因被记录在ERR$_DEST 表
COLUMN ora_err_mesg$ FORMAT A70
SELECT ora_err_number$, ora_err_mesg$
FROM   err$_dest
WHERE  ora_err_tag$ = 'UPDATE';

ORA_ERR_NUMBER$ ORA_ERR_MESG$
--------------- ---------------------------------------------------------
           1400 ORA-01400: cannot insert NULL into ("TEST"."DEST"."CODE")
           1400 ORA-01400: cannot insert NULL into ("TEST"."DEST"."CODE")

3、MERGE 操作
下面的代码从dest表删除一些行，然后尝试从source 表合并数据到dest表

DELETE FROM dest
WHERE  id > 50000;

MERGE INTO dest a
    USING source b
    ON (a.id = b.id)
  WHEN MATCHED THEN
    UPDATE SET a.code        = b.code,
               a.description = b.description
  WHEN NOT MATCHED THEN
    INSERT (id, code, description)
    VALUES (b.id, b.code, b.description);
                  *
ERROR at line 9:
ORA-01400: cannot insert NULL into ("TEST"."DEST"."CODE")


--merge操作同样由于not null约束导致导致操作失败并且回滚。
--下面为其添加DML error logging 允许merge操作完成
MERGE INTO dest a
    USING source b
    ON (a.id = b.id)
  WHEN MATCHED THEN
    UPDATE SET a.code        = b.code,
               a.description = b.description
  WHEN NOT MATCHED THEN
    INSERT (id, code, description)
    VALUES (b.id, b.code, b.description)
  LOG ERRORS INTO err$_dest ('MERGE') REJECT LIMIT UNLIMITED;

99998 rows merged.


--更新操作失败的行以及失败原因同样被记录在ERR$_DEST 表中
COLUMN ora_err_mesg$ FORMAT A70
SELECT ora_err_number$, ora_err_mesg$
FROM   err$_dest
WHERE  ora_err_tag$ = 'MERGE';

ORA_ERR_NUMBER$ ORA_ERR_MESG$
--------------- ---------------------------------------------------------
           1400 ORA-01400: cannot insert NULL into ("TEST"."DEST"."CODE")
           1400 ORA-01400: cannot insert NULL into ("TEST"."DEST"."CODE")

2 rows selected.

4、DELETE 操作
DEST_CHILD 表有一个到dest表的外键约束，因此如果我们基于DEST表添加一些数据到dest_child，然后从dest删除记录将产生错误。

INSERT INTO dest_child (id, dest_id) VALUES (1, 100);
INSERT INTO dest_child (id, dest_id) VALUES (2, 101);

DELETE FROM dest;
*
ERROR at line 1:
ORA-02292: integrity constraint (TEST.DEST_CHILD_DEST_FK) violated - child record found



--对于Delete操作，同样可以添加DML error logging子句来记录错误使得整个语句成功执行 。
DELETE FROM dest
LOG ERRORS INTO err$_dest ('DELETE') REJECT LIMIT UNLIMITED;

99996 rows deleted.


--下面是Delete操作失败的日志以及错误原因。
COLUMN ora_err_mesg$ FORMAT A69
SELECT ora_err_number$, ora_err_mesg$
FROM   err$_dest
WHERE  ora_err_tag$ = 'DELETE';

ORA_ERR_NUMBER$ ORA_ERR_MESG$
--------------- ---------------------------------------------------------------------
           2292 ORA-02292: integrity constraint (TEST.DEST_CHILD_DEST_FK) violated -
                child record found

           2292 ORA-02292: integrity constraint (TEST.DEST_CHILD_DEST_FK) violated -
                child record found

2 rows selected.

四、后记
       1、DML error logging特性使用了自治事务，因此不论当前的主事务是提交或回滚，其产生的错误信息都将记录在对应的日志表。
       2、DML error logging使得错误处理得以高效实现，尽管如此，如果在操作中，很多表需要DML操作，尤其是数据迁移时，使得每一个表都
               需要创建一个对应的日志表。做了一个测试，可以将日志表的一些基表列删除，保留主要列，日志依然可以成功记录以缩小日志大小。
       3、能否将多张日志表合并到一张日志表，然后每一行数据中添加对应的表名以及主键等信息以鉴别错误，这样子的话，仅仅用少量的日志
               表即可实现记录多张表上的DML error。这个还没有来得及测试，This is a question。

五、使用FORALL 的SAVE EXCEPTIONS子句示例

FORALL 之 SAVE EXCEPTIONS 子句应用一例