How to find the last error codes and messages of failed statements executed by a user

monitoringoracle

Hopefully, a program that connects to a Oracle DB does sufficient logging of errors on its own, e.g. when doing a bulk insert the error reporting of a program could produce something like this:

ORA-24381: error(s) in array DML
statement: insert into BARTABLE(...) ....
row 5: code=12096, message=ORA-12096: error in materialized view log on "FOO"."BARTABLE"
  ORA-12899: value too large for column "FOO"."MLOG$_BARTABLE"."XCOL" 
    (actual: 50, maximum: 100)

But sometimes it is not possible to change all existing DB client programs.

For that usecase – where to look up the same information inside the database?

Perhaps there are some 'virtual' oracle tables/views that one can use to get the last x error messages/codes for a certain user …

Ideally also including the corresponding SQL statement.

Best Answer

You can create an AFTER SERVERERROR trigger and log the relevant information to a table.

Here is an example: http://www.adp-gmbh.ch/ora/sql/trigger/servererror.html

Related Question