Oracle DBMS_ERRLOG Demos

Oracle DBMS_ERRLOG Version 11.2.0.3

General Information

Note

... 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_OUTPUT	DBMS_UTILITY	PLITBLM

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

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 allows you to manually create your own error log table so that you can control the columns that are captured as demonstrated here:

conn uwclass/uwclass

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;

And 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 you to see the values in the row, or at least the primary key, and to fix the problem. Knowing that an exception took place, but you can not track it down and do anything about it, is not a great idea. So stick to the default usage and you will have a far superior implementation.