Oracle | DBMS

Oracle DBMS_ERRLOG Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose Provides a procedure that enables creating an error logging table so that DML operations can continue after encountering errors rather than performing an abort and rollback. Tables with LONG, CLOB, BLOB, BFILE, and ADT data types are not supported.

... LOG ERRORS - effectively it turns array processing into single row processing, so it adds an expense at the moment of inserting, even though it saves you the overhead of an array rollback if a duplicate gets into the data.
~ Jonathan Lewis / comp.databases.oracle.server / 13-Aug-2006

AUTHID CURRENT_USER

Dependencies

DBMS_ASSERT	DBMS_SQL	KUPD$DATA
DBMS_LOB	DBMS_STANDARD	PLITBLM
DBMS_OUTPUT	DBMS_UTILITY

Documented Yes: Packages and Types Reference

First Available 10.2

Security Model Owned by SYS with EXECUTE granted to PUBLIC

Source {ORACLE_HOME}/rdbms/admin/dbmserlg.sql

CREATE_ERROR_LOG

Variable Definition 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);

conn uwclass/uwclass@pdbdev

      

      CREATE TABLE t AS

      SELECT *

      FROM all_tables

      WHERE 1=2;

      

      ALTER TABLE t

      ADD CONSTRAINT pk_t

      PRIMARY KEY (owner, table_name)

      USING INDEX;

      

      ALTER TABLE t

      ADD CONSTRAINT cc_t

      CHECK (blocks < 11);

      

      col blocks format 99999

      

      SELECT blocks, COUNT(*)

      FROM all_tables

      GROUP BY blocks

      HAVING COUNT(*) > 1

      ORDER BY 2,1;

      

      INSERT /*+ APPEND */ INTO t

      SELECT *

      FROM all_tables;

      

      SELECT COUNT(*) FROM t;

      

      ------------------------------

      

      exec dbms_errlog.create_error_log('T');

      

      desc err$_t

      

      INSERT /*+ APPEND */ INTO t

      SELECT *

      FROM all_tables

      LOG ERRORS

      REJECT LIMIT UNLIMITED;

      

      SELECT COUNT(*) FROM t;

      

      COMMIT;

      

      SELECT COUNT(*) FROM t;

      

      SELECT COUNT(*) FROM err$_t;

      

      set linesize 121

      col table_name format a30

      col blocks format a7

      col ora_err_mesg$ format a60

      

      SELECT ora_err_mesg$, table_name, blocks

      FROM err$_t;

Manually error log creation Oracle makes it possible to manually create an error log table so that you can control the columns that are captured as demonstrated here:

conn uwclass/uwclass@pdbdev CREATE TABLE error_log_servers ( ora_err_number$ NUMBER, ora_err_mesg$ VARCHAR2(2000), ora_err_rowid$ ROWID, ora_err_optyp$ VARCHAR2(2), ora_err_tag$ VARCHAR2(2000)); INSERT INTO servers (srvr_id) VALUES (1) LOG ERRORS INTO ERROR_LOG_SERVERS; col ora_err_mesg$ format a10 col ora_err_tag$ format a50 SELECT * FROM error_log_servers; DROP TABLE error_log_servers PURGE; CREATE TABLE error_log_servers ( ora_err_number$ NUMBER, ora_err_mesg$ VARCHAR2(2000), ora_err_rowid$ ROWID, ora_err_optyp$ VARCHAR2(2), ora_err_tag$ VARCHAR2(2000), srvr_id NUMBER(10)); INSERT INTO servers (srvr_id) VALUES (1) LOG ERRORS INTO ERROR_LOG_SERVERS; SELECT * FROM error_log_servers;

It has been suggested in the OTN forums that this capability might be useful, in its most generic form, for creating a single generic error table rather than one table for each base table being accessed for DML.

From my perspective the point of having an error log table is to allow viewing row values, or at least their primary key, to enable a fix. Knowing that an exception took place, but not being able to track it down and do anything about it, is of little or no value. So it is recomended that developers use the default himplementation.