Auditing an Oracle constraint violation?

As one of the options you could create a schema level trigger to catch database errors. Here is an example:

-- our test tableSQL> create table TB_Test(  2    col1 number primary key,  3    col2 number,  4    col3 number,  5    constraint CHK_CONS check (col3 < 5)  6  )  7  ; -- table we going to log errors into   SQL> create table Db_Errors(  2    msg varchar2(1000)  3  )  4  ;Table created-- schema level trigger, which fires when -- exception of certain code is raised.-- In this case, we filter exceptions' codes out in the when clause -- of the trigger.-- 1     - unique constraint violation-- 2290  - check constraint violation-- 2292  - foreign key constraint violation-- you can add moreSQL> create or replace trigger TR_CatchErrors  2  after servererror on schema  3  when (ora_server_error(1) in (1, 2290, 2292))  4  begin  5    insert into Db_Errors(Msg)  6      values(ora_server_error_msg(1));  7  end;  8  /Trigger created-- Test case;-- 1  check constraint violation SQL> insert into tb_test(col1, col2, col3)  2    values(1, 2, 6);insert into tb_test(col1, col2, col3)  values(1, 2, 6)ORA-02290: check constraint (HR.CHK_CONS) violated-- unique constraint violationSQL> insert into tb_test(col1, col2, col3)  2    values(1, 2, 4);insert into tb_test(col1, col2, col3)  values(1, 2, 4)ORA-00001: unique constraint (HR.SYS_C0014608) violated-- And here what we have in our DB_Errors table loggedSQL> select msg   2    from db_errors;MSG--------------------------------------------------------------------------------ORA-02290: check constraint (HR.CHK_CONS) violatedORA-00001: unique constraint (HR.SYS_C0014608) violated

As another option(starting from 10gR2), you can use log errors clause of a DML statement. But it would require you to provide log errors clause for each dml statement. Here is an example:

-- create a table for error logging-- using create_error_log() procedure of dbms_errlog package-- for a specific table. You could also create your own custom -- error logging tablebegin  dbms_errlog.create_error_log('TB_TEST');end;-- unique constraint violationSQL> insert into tb_test(col1, col2, col3)  2    values(1, 2, 3)  3  log errors reject limit unlimited  4  ;0 rows insertedSQL> select ora_err_mesg$  2    from err$_tb_test t  3  ;ORA_ERR_MESG$--------------------------------------------------------------------------------ORA-00001: unique constraint (HR.SYS_C0014608) violated