Oracle Scratchpad

August 24, 2018

Error Logging

Filed under: Oracle,Troubleshooting — Jonathan Lewis @ 11:19 am BST Aug 24,2018

Error logging is a topic that I’ve mentioned a couple of times in the past, most recently as a follow-up in a discussion of the choices for copying a large volume of data from one table to another, but originally in an addendum about a little surprise you may get when you use extended strings (max_string_size = EXTENDED).

If you use the default call to dbms_errlog.create_error_log() to create an error logging table then Oracle will create a table with a few columns of its own plus every column (name) that you have in your original table – but it will create your columns as varchar2(4000), or nvarchar2(2000), or raw(2000) – unless you’ve set the max_string_size to extended.  Here’s a simple  demo script with results from two different systems, one with the default setting the other with the extended setting (note, there’s a little inconsistency in handling raw() columns.

rem     Script:         log_errors_min.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Jun 2018
rem     Purpose:

create table t1 (
        v1      varchar2(10),
        n1      number(2,0),
        d1      date,
        nv1     nvarchar2(10),
        r1      raw(10)

execute dbms_errlog.create_error_log('t1')

desc err$_t1

max_string_size = STANDARD
 Name			       Null?	Type
 ----------------------------- -------- --------------------
 V1					VARCHAR2(4000)
 N1					VARCHAR2(4000)
 D1					VARCHAR2(4000)
 NV1					NVARCHAR2(2000)
 R1					RAW(2000)

max_string_size = EXTENDED
 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)
 V1                                     VARCHAR2(32767)
 N1                                     VARCHAR2(32767)
 D1                                     VARCHAR2(32767)
 NV1                                    NVARCHAR2(16383)
 R1                                     RAW(32767)

Every single “original” column that appears in this table will be a LOB, with an inline LOB locator of 30 or more bytes. (At least, that’s the implementation, I haven’t checked for 12.2 or 18.3).

If this is going to be a problem (e.g. you have a table defined with 500 columns but only use 120 of them) you can create a minimalist error logging table. Provided you create it with the ora_err% columns suitably defined you can add only those columns you’re really interested in (or feel threatened by), and you don’t have to declare them at extreme lengths. e.g.

create table err$_special (
        ora_err_number$         number,
        ora_err_mesg$           varchar2(2000),
        ora_err_rowid$          rowid,
        ora_err_optyp$          varchar2(2),
        ora_err_tag$            varchar2(2000),
        n1                      varchar2(128)

insert into t1 values(1,'abc','02-jan-1984',sys_op_c2c('abc'),hextoraw('0xFF')) 
log errors into err$_special
reject limit unlimited

execute print_table('select * from err$_special')

ORA_ERR_NUMBER$               : 1722
ORA_ERR_MESG$                 : ORA-01722: invalid number

ORA_ERR_ROWID$                :
ORA_ERR_OPTYP$                : I
ORA_ERR_TAG$                  :
N1                            : abc

If you try to create an error logging table that doesn’t include the 5 critical columns you’ll see Oracle error ORA-38900: missing mandatory column “ORA_ERR_{something}” of error log table “{your logging table name}” when you try to log errors into it, and the 5 critical columns have to be the first 5 columns (in any order) in the table or you’ll get Oracle error ORA-38901: column “ORA_ERR_{something}$” of table “{your logging table name}” when you try to log errors into it.


  1. I also like to remind users of this feature that you can ADD columns – such as who caused the error, where it happened in your code and WHEN it happened. Critical pieces of information LOG ERRORS does not record automatically. Check out my helper package on LiveSQL for a procedure to create the error log table with these additional columns, along with a trigger to populate them.

    Comment by Steven Feuerstein (@sfonplsql) — August 31, 2018 @ 12:08 pm BST Aug 31,2018 | Reply

  2. I spotted a large value of “failed parse elapsed time” recently on our 12c where we have max_string_size = STANDARD. The EVENTS ‘10035’ showed up it is the CREATE TABLE …. VARCHAR2(32767) coming from dbms_errlog.create_error_log.
    So we did SQL_TRACE. There is not a pre-check inside of this procedure regarding the value of max_string_size. It just tries to create a table with VARCHAR2(32767), and if it fails, then the 2-nd try is with VARCHAR2(4000).

    Comment by Jan S. — September 11, 2020 @ 9:08 am BST Sep 11,2020 | Reply

    • Jan,

      Thanks very much for flagging this up, and that’s a useful reminde about being able to use event 10035 to dump this type of error to the alert log in versions prior to 12.2

      I have to say, though, ,that it does sound a little strange that the application should be executing enough create _error_log calls for the statement to show up as frequently parse.

      Jonathan Lewis

      Comment by Jonathan Lewis — September 11, 2020 @ 10:49 am BST Sep 11,2020 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by