阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

Oracle DML容错处理方法

181次阅读
没有评论

共计 16360 个字符,预计需要花费 41 分钟才能阅读完成。

Oracle dml 操作过程中可能出现键重复或者数据类型不一致等问题,一般进行数据处理时候需要对这些可能出现的错误提前考虑,避免更新失败。Oralce 给出了一些其他解决方案,以在不同场景下使用。

1、ignore_row_on_dupkey_index HINT

Oracle 11.2.0.1 版本中心加入的 3 个提示 CHANGE_DUPKEY_ERROR_INDEX, IGNORE_ROW_ON_DUPKEY_INDEX, RETRY_ON_ROW_CHANGE,与其他提示不同,特别之处在于存在 ” 语义效果(semantic effect)”。

在 insert into tablea …select * from tbl 中,如果存在唯一约束,会导致整个 insert 操作失败。使用 IGNORE_ROW_ON_DUPKEY_INDEX 提示,会忽略唯一约束冲突,回滚当前行,继续完成其他行的插入。

示例:

数据准备:

create table emp1(empno number primary key,ename varchar2(50));

insert into emp1(empno,ename) select empno,ename from emp;

commit;

emp1 表存在 empno 主键.

再次插入:

insert into emp1(empno,ename) select empno,ename from emp;

提示错误:

ORA-00001: 违反唯一约束条件 (SCOTT.SYS_C0013035)

使用 HINT:

insert /*+ignore_row_on_dupkey_index(emp1,SYS_C0013035)*/into emp1(empno,ename) select empno,ename from emp;

提示:插入 0 行;

SYS_C0013035: 创建主键时 oracle 自动生成的索引。

2、使用 dbms_errlog 包

说明:10g 后可用,不支持 LONG, CLOB, BLOB, BFILE, ADT 数据类型

创建错误日志表

begin

dbms_errlog.create_error_log(dml_table_name => ‘EMP1’,

err_log_table_name => ‘T_ERR_LOG’,

err_log_table_owner => user,

err_log_table_space => ‘users’,

skip_unsupported => true);

end;

参数说明:

Parameter

Description

dml_table_name

The name of the DML table to base the error logging table on. The name can be fully qualified (for example, empscott.emp“EMP”“SCOTT”.”EMP”). If a name component is enclosed in double quotes, it will not be upper cased.

err_log_table_name

The name of the error logging table you will create.

The default is the first 25 characters in the name of the DML table prefixed with‘ERR$_’. Examples are the following:

dml_table_name‘EMP’err_log_table_name‘ERR$_EMP’

dml_table_name‘”Emp2″‘err_log_table_name‘ERR$_Emp2’

err_log_table_owner

The name of the owner of the error logging table. You can specify the owner indml_table_name. Otherwise, the schema of the current connected user is used.

err_log_table_space

The tablespace the error logging table will be created in. If not specified, the default tablespace for the user owning the DML error logging table will be used.

skip_unsupported

When set to TRUE, column types that are not supported by error logging will be skipped over and not added to the error logging table.

When set to FALSE, an unsupported column type will cause the procedure to terminate.

The default is FALSE.

 

对于不支持的数据类型可以使用最后一个参数控制,如果为 true,不支持类型字段将不会进入错误日志表。如果是 false,在遇到不支持类型字段时执行包会报错。

各参数默认值如下:

DBMS_ERRLOG.CREATE_ERROR_LOG (

dml_table_name IN VARCHAR2,

err_log_table_name IN VARCHAR2 := NULL,

err_log_table_owner IN VARCHAR2 := NULL,

err_log_table_space IN VARCHAR2 := NULL,

skip_unsupported IN BOOLEAN := FALSE);

注意:再次执行该包会报错,执行前须确认错误记录表不存在。

dml 使用错误日志表

insert into emp1(empno,ename) select empno,ename from emp log errors into t_err_log reject limit unlimited;

注意红色字体部分。Limimt 后面可以是具体数字,表示容错行数

语法:

LOG ERRORS [INTO [schema.]table] [(‘simple_expression’)] [REJECT LIMIT integer|UNLIMITED]

simple_expression:用来标记 t_err_log 表 ora_err_tag$ 字段信息

Update、merge 和 delete 也可以使用该方法。

更新完检查 t_err_log 失败记录及错误原因。

更多详情见请继续阅读下一页的精彩内容 :http://www.linuxidc.com/Linux/2017-01/139577p2.htm

关于 DML Error Logging 效率的问题,摘自网上一篇文章,作为单独一篇说明,原文如下:

DML Error Logging in Oracle 10g Database Release 2

In some situations the most obvious solution to a problem is a DML statement (INSERT … SELECTUPDATEDELETE), but you may choose to avoid DML because of the way it reacts to exceptions. By default, when a DML statement fails the whole statement is rolled back, regardless of how many rows were processed successfully before the error was detected. In the past, the only way around this problem was to process each row individually, preferably with a bulk operation using FORALL and the SAVE EXCEPTIONS clause. In Oracle 10g Database Release 2, the DML error logging feature has been introduced to solve this problem. Adding the appropriate LOG ERRORS clause on to most INSERT, UPDATE, MERGE and DELETE statements enables the operations to complete, regardless of errors. This article presents an overview of the DML error logging functionality, with examples of each type of DML statement.

  • Syntax
  • Restrictions
  • Sample Schema
  • Insert
  • Update
  • Merge
  • Delete
  • Performance

Syntax

The syntax for the error logging clause is the same for INSERT, UPDATE, MERGE and DELETE statements.

LOG ERRORS [INTO [schema.]table] [(‘simple_expression’)] [REJECT LIMIT integer|UNLIMITED]

The optional INTO clause allows you to specify the name of the error logging table. If you omit this clause, the the first 25 characters of the base table name are used along with the “ERR$_” prefix.

The simple_expression is used to specify a tag that makes the errors easier to identify. This might be a string or any function whose result is converted to a string.

The REJECT LIMIT is used to specify the maximum number of errors before the statement fails. The default value is 0 and the maximum values is the keyword UNLIMITED. For parallel DML operations, the reject limit is applied to each parallel server.

Restrictions

The DML error logging functionality is not invoked when:

  • Deferred constraints are violated.
  • Direct-path INSERT or MERGE operations raise unique constraint or index violations.
  • UPDATE or MERGE operations raise a unique constraint or index violation.

In addition, the tracking of errors in LONG, LOB and object types is not supported, although a table containing these columns can be the target of error logging.

Sample Schema

This following code creates and populates the tables necessary to run the example code in this article.

— 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)

);

Notice that the CODE column is optional in the SOURCE table and mandatory in the DEST table.

Once the basic tables are in place we can create a table to hold the DML error logs for the DEST. A log table must be created for every base table that requires the DML error logging functionality. This can be done manually or with the CREATE_ERROR_LOG procedure in the DBMS_ERRLOG package, as shown below.

— Create the error logging table.

BEGIN

DBMS_ERRLOG.create_error_log (dml_table_name => ‘dest’);

END;

/

 

PL/SQL procedure successfully completed.

 

SQL>

The owner, name and tablespace of the log table can be specified, but by default it is created in the current schema, in the default tablespace with a name that matches the first 25 characters of the base table with the “ERR$_” prefix.

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>

The structure of the log table includes maximum length and datatype independent versions of all available columns from the base table, as seen below.

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)

 

SQL>

Insert

When we built the sample schema we noted that the CODE column is optional in the SOURCE table, but mandatory in th DEST table. When we populated the SOURCE table we set the code to NULL for two of the rows. If we try to copy the data from the SOURCE table to the DEST table we get the following result.

INSERT INTO dest

SELECT *

FROM source;

 

SELECT *

*

ERROR at line 2:

ORA-01400: cannot insert NULL into (“TEST”.”DEST”.”CODE”)

 

 

SQL>

The failure causes the whole insert to roll back, regardless of how many rows were inserted successfully. Adding the DML error logging clause allows us to complete the insert of the valid rows.

INSERT INTO dest

SELECT *

FROM source

LOG ERRORS INTO err$_dest (‘INSERT’) REJECT LIMIT UNLIMITED;

 

99998 rows created.

 

SQL>

The rows that failed during the insert are stored in the ERR$_DEST table, along with the reason for the failure.

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.

 

SQL>

Update

The following code attempts to update the CODE column for 10 rows, setting it to itself for 8 rows and to the value NULL for 2 rows.

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

 

 

SQL>

As expected, the statement fails because the CODE column is mandatory. Adding the DML error logging clause allows us to complete the update of the valid rows.

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.

 

SQL>

The rows that failed during the update are stored in the ERR$_DEST table, along with the reason for the failure.

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”)

 

2 rows selected.

 

SQL>

Merge

The following code deletes some of the rows from the DEST table, then attempts to merge the data from the SOURCE table into the DEST table.

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”)

 

 

SQL>

As expected, the merge operation fails and rolls back. Adding the DML error logging clause allows the merge operation to complete.

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.

 

SQL>

The rows that failed during the update are stored in the ERR$_DEST table, along with the reason for the failure.

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.

 

SQL>

Delete

The DEST_CHILD table has a foreign key to the DEST table, so if we add some data to it would would expect an error if we tried to delete the parent rows from the DEST table.

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

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

With the child data in place we ca attempt to delete th data from the DEST table.

DELETE FROM dest;

*

ERROR at line 1:

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

 

 

SQL>

As expected, the delete operation fails. Adding the DML error logging clause allows the delete operation to complete.

DELETE FROM dest

LOG ERRORS INTO err$_dest (‘DELETE’) REJECT LIMIT UNLIMITED;

 

99996 rows deleted.

 

SQL>

The rows that failed during the delete operation are stored in the ERR$_DEST table, along with the reason for the failure.

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.

 

SQL>

Performance

The performance of DML error logging depends on the way it is being used and what version of the database you use it against. Prior to Oracle 12c, you will probably only use DML error logging during direct path loads, since conventional path loads become very slow when using it. The following example displays this, but before we start we will need to remove the extra dependency table.

DROP TABLE dest_child PURGE;

Truncate the destination table and run a conventional path load using DML error logging, using SQL*Plus timing to measure the elapsed time.

SET TIMING ON

 

TRUNCATE TABLE dest;

 

INSERT INTO dest

SELECT *

FROM source

LOG ERRORS INTO err$_dest (‘INSERT NO-APPEND’) REJECT LIMIT UNLIMITED;

 

99998 rows created.

 

Elapsed: 00:00:08.61

SQL>

Next, repeat the test using a direct path load this time.

TRUNCATE TABLE dest;

 

INSERT /*+ APPEND */ INTO dest

SELECT *

FROM source

LOG ERRORS INTO err$_dest (‘INSERT APPEND’) REJECT LIMIT UNLIMITED;

 

99998 rows created.

 

Elapsed: 00:00:00.38

SQL>

Finally, perform the same load using FORALL … SAVE EXCEPTIONS method.

TRUNCATE TABLE dest;

 

DECLARE

TYPE t_tab IS TABLE OF dest%ROWTYPE;

l_tab t_tab;

 

l_start PLS_INTEGER;

 

CURSOR c_source IS

SELECT * FROM source;

 

ex_dml_errors EXCEPTION;

PRAGMA EXCEPTION_INIT(ex_dml_errors, -24381);

BEGIN

 

OPEN c_source;

LOOP

FETCH c_source

BULK COLLECT INTO l_tab LIMIT 1000;

EXIT WHEN l_tab.count = 0;

 

BEGIN

FORALL i IN l_tab.first .. l_tab.last SAVE EXCEPTIONS

INSERT INTO dest VALUES l_tab(i);

EXCEPTION

WHEN ex_dml_errors THEN

NULL;

END;

END LOOP;

CLOSE c_source;

END;

/

 

PL/SQL procedure successfully completed.

 

Elapsed: 00:00:01.01

SQL>

From this we can see that DML error logging is very fast for direct path loads, but does not perform well for conventional path loads. In fact, it performs significantly worse than the FORALL … SAVE EXCEPTIONS method.

The relative performance of these methods depends on the database version. The following table shows the results of the previous tests against a number of database versions. They are run on different servers, so don’t compare version-to-version. Look at the comparison between the methods within a version.

10.2.0.4 11.2.0.3 11.2.0.4 12.1.0.1

======== ======== ======== ========

DML Error Logging : 07.62 08.61 04.82 00.94

DML Error Logging (APPEND) : 00.86 00.38 00.85 01.07

FORALL … SAVE EXCEPTIONS : 01.15 01.01 00.94 01.37

Link: https://oracle-base.com/articles/10g/dml-error-logging-10gr2

 

同样的方式不同数据库版本性能可能不同,同版本数据库下,使用直接路径加

更多 Oracle 相关信息见 Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址 :http://www.linuxidc.com/Linux/2017-01/139577.htm

Oracle dml 操作过程中可能出现键重复或者数据类型不一致等问题,一般进行数据处理时候需要对这些可能出现的错误提前考虑,避免更新失败。Oralce 给出了一些其他解决方案,以在不同场景下使用。

1、ignore_row_on_dupkey_index HINT

Oracle 11.2.0.1 版本中心加入的 3 个提示 CHANGE_DUPKEY_ERROR_INDEX, IGNORE_ROW_ON_DUPKEY_INDEX, RETRY_ON_ROW_CHANGE,与其他提示不同,特别之处在于存在 ” 语义效果(semantic effect)”。

在 insert into tablea …select * from tbl 中,如果存在唯一约束,会导致整个 insert 操作失败。使用 IGNORE_ROW_ON_DUPKEY_INDEX 提示,会忽略唯一约束冲突,回滚当前行,继续完成其他行的插入。

示例:

数据准备:

create table emp1(empno number primary key,ename varchar2(50));

insert into emp1(empno,ename) select empno,ename from emp;

commit;

emp1 表存在 empno 主键.

再次插入:

insert into emp1(empno,ename) select empno,ename from emp;

提示错误:

ORA-00001: 违反唯一约束条件 (SCOTT.SYS_C0013035)

使用 HINT:

insert /*+ignore_row_on_dupkey_index(emp1,SYS_C0013035)*/into emp1(empno,ename) select empno,ename from emp;

提示:插入 0 行;

SYS_C0013035: 创建主键时 oracle 自动生成的索引。

2、使用 dbms_errlog 包

说明:10g 后可用,不支持 LONG, CLOB, BLOB, BFILE, ADT 数据类型

创建错误日志表

begin

dbms_errlog.create_error_log(dml_table_name => ‘EMP1’,

err_log_table_name => ‘T_ERR_LOG’,

err_log_table_owner => user,

err_log_table_space => ‘users’,

skip_unsupported => true);

end;

参数说明:

Parameter

Description

dml_table_name

The name of the DML table to base the error logging table on. The name can be fully qualified (for example, empscott.emp“EMP”“SCOTT”.”EMP”). If a name component is enclosed in double quotes, it will not be upper cased.

err_log_table_name

The name of the error logging table you will create.

The default is the first 25 characters in the name of the DML table prefixed with‘ERR$_’. Examples are the following:

dml_table_name‘EMP’err_log_table_name‘ERR$_EMP’

dml_table_name‘”Emp2″‘err_log_table_name‘ERR$_Emp2’

err_log_table_owner

The name of the owner of the error logging table. You can specify the owner indml_table_name. Otherwise, the schema of the current connected user is used.

err_log_table_space

The tablespace the error logging table will be created in. If not specified, the default tablespace for the user owning the DML error logging table will be used.

skip_unsupported

When set to TRUE, column types that are not supported by error logging will be skipped over and not added to the error logging table.

When set to FALSE, an unsupported column type will cause the procedure to terminate.

The default is FALSE.

 

对于不支持的数据类型可以使用最后一个参数控制,如果为 true,不支持类型字段将不会进入错误日志表。如果是 false,在遇到不支持类型字段时执行包会报错。

各参数默认值如下:

DBMS_ERRLOG.CREATE_ERROR_LOG (

dml_table_name IN VARCHAR2,

err_log_table_name IN VARCHAR2 := NULL,

err_log_table_owner IN VARCHAR2 := NULL,

err_log_table_space IN VARCHAR2 := NULL,

skip_unsupported IN BOOLEAN := FALSE);

注意:再次执行该包会报错,执行前须确认错误记录表不存在。

dml 使用错误日志表

insert into emp1(empno,ename) select empno,ename from emp log errors into t_err_log reject limit unlimited;

注意红色字体部分。Limimt 后面可以是具体数字,表示容错行数

语法:

LOG ERRORS [INTO [schema.]table] [(‘simple_expression’)] [REJECT LIMIT integer|UNLIMITED]

simple_expression:用来标记 t_err_log 表 ora_err_tag$ 字段信息

Update、merge 和 delete 也可以使用该方法。

更新完检查 t_err_log 失败记录及错误原因。

更多详情见请继续阅读下一页的精彩内容 :http://www.linuxidc.com/Linux/2017-01/139577p2.htm

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计16360字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中