79 DBMS_ERRLOG
The DBMS_ERRLOG
package provides a procedure that enables you to create an error logging table so that DML operations can continue after encountering errors rather than abort and roll back. This enables you to save time and system resources.
This chapter contains the following topics:
See Also:
Oracle Database Data Warehousing Guide for more information regarding how to use DBMS_ERRLOG
and Oracle Database SQL Language Reference for error_logging_clause
syntax
79.1 DBMS_ERRLOG Security Model
Security on this package can be controlled by granting EXECUTE
on this package to selected users or roles. The EXECUTE
privilege is granted publicly. However, to create an error logging table, you need SELECT
access on the base table or view, the CREATE
TABLE
privilege, as well as tablespace quota for the target tablespace.
79.2 Summary of DBMS_ERRLOG Subprograms
The DBMS_ERRORLOG
package includes the CREATE_ERROR_LOG procedure subprogram.
Table 79-1 DBMS_ERRLOG Package Subprograms
Subprogram | Description |
---|---|
Creates the error logging table used in DML error logging |
79.2.1 CREATE_ERROR_LOG Procedure
This procedure creates the error logging table needed to use the DML error logging capability.
LONG
, CLOB
, BLOB
, BFILE
, and ADT
datatypes are not supported in the columns.
Syntax
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);
Parameters
Table 79-2 CREATE_ERROR_LOG Procedure Parameters
Parameter | Description |
---|---|
|
The name of the DML table to base the error logging table on. The name can be fully qualified (for example, |
|
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
|
|
The name of the owner of the error logging table. You can specify the owner in |
|
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. |
|
When set to When set to The default is |
Examples
First, create an error log table for the channels
table in the SH
schema, using the default name generation.
Then, see all columns of the table channels
:
SQL> DESC channels Name Null? Type --------------------------- ------- ----- CHANNEL_ID NOT NULL CHAR(1) CHANNEL_DESC NOT NULL VARCHAR2(20) CHANNEL_CLASS VARCHAR2(20)
Finally, see all columns of the generated error log table. Note the mandatory control columns that are created by the package:
SQL> DESC ERR$_CHANNELS 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) CHANNEL_ID VARCHAR2(4000) CHANNEL_DESC VARCHAR2(4000) CHANNEL_CLASS VARCHAR2(4000)
See Oracle Database Administrator's Guide for more information regarding control columns.