Log all database errors to a table in Oracle

Log all database errors to a table in Oracle

create table log_all_errors (
    error     varchar2(30),
    timestamp date,
    username  varchar2(30),
        osuser    varchar2(30),
        machine   varchar2(64),
    process   varchar2(8),
    program   varchar2(48));

Connect on the server as sysdba 
 
$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jul 17 14:44:58 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> GRANT SELECT ON SYS.V_$SESSION TO  <USER>;

Grant succeeded.

SQL>


create or replace trigger log_all_errors
    after servererror on database
declare
    var_user     varchar2(30);
    var_osuser   varchar2(30);
    var_machine  varchar2(64);
    var_process  varchar2(8);
    var_program  varchar2(48);
begin
    select username, osuser, machine, process, program
    into   var_user, var_osuser, var_machine, var_process, var_program
    from   sys.v_$session
    where  audsid = userenv('sessionid');

    insert into log_all_errors
      values(dbms_standard.server_error(1),sysdate,var_user,
             var_osuser,var_machine,var_process,var_program);
end;
/


Now this trigger logs all the errors on the database to the table log_all_errors.