Creates a table to capture data from v$session whenever any

database error activity occurs.
Capture scope can be widened to include additional session information

rem -----------------------------------------------------------------------
rem Filename:   database_err.sql
rem Purpose:    Captures all database errors to a table
rem             Oracle8i and 9i 
rem Date:       May 25, 2003
rem Author:     Laurence Jennings ([email protected])
rem -----------------------------------------------------------------------

drop trigger catch_err_trigger;
drop table   save_err_audit;

create table save_err_audit (
error     varchar2(30),
timestamp date,
username  varchar2(30),
       osuser    varchar2(30),
       machine   varchar2(64),
process   varchar2(15),
program   varchar2(64));

create or replace trigger 
         catch_err_trigger 
  after servererror on database
declare
v_user     varchar2(30);
v_osuser   varchar2(30);
v_machine  varchar2(64);
v_process  varchar2(8);
v_program  varchar2(48);
begin select username, osuser, machine, process, program
into v_user, v_osuser, v_machine, v_process, v_program
from sys.v_$session where audsid = userenv('sessionid');

insert into save_err_audit values(dbms_standard.server_error(1),
                sysdate,v_user,
         v_osuser,
                v_machine,
                v_process,
                v_program);
end;
/

جميلة جدا اخى الفاضل