Oracle DML容错处理（2）

In some situations the most obvious solution to a problem is a DML statement (INSERT ... SELECT, UPDATE, DELETE), 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.

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.

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.

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.

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.

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.