Capture any failed SQL queries using database trigger in Oracle

Создаем таблицу куда будем записывать запросы с ошибками:

CONNECT SYSTEM;

DROP TABLE servererror_log;

CREATE TABLE servererror_log (
  error_datetime  TIMESTAMP,
  error_user      VARCHAR2(30),
  db_name         VARCHAR2(30),
  error_stack     VARCHAR2(2000),
  call_stack      VARCHAR2(2000),
  SID             NUMBER,
  SQL_ID          VARCHAR2 (13),
  sql_statement   VARCHAR2(2000),
  sql_statement_all   CLOB,
  CLIENT_INFO  VARCHAR2(256 BYTE),
  CURR_SCHEMA  VARCHAR2(256 BYTE),
  CURR_USER    VARCHAR2(256 BYTE),
  CURR_DB_NAME VARCHAR2(256 BYTE),
  HOST         VARCHAR2(256 BYTE),
  IP           VARCHAR2(256 BYTE),
  OSUSER       VARCHAR2(256 BYTE),
  SESSID       VARCHAR2(256 BYTE),
  SESS_USER    VARCHAR2(256 BYTE),
  TERMINAL     VARCHAR2(256 BYTE)
);

GRANT SELECT, INSERT, UPDATE, DELETE ON SYSTEM.SERVERERROR_LOG TO SCOTT;

Выдаем явный грант на V_$SESSION пользователю SYSTEM для того чтобы триггер log_server_errors указанный ниже скомпилировался.

CONNECT SYS AS SYSDBA;
GRANT SELECT ON V_$SESSION TO SYSTEM;

Триггер базы данных на SERVERERROR(в условии WHEN можем исключить ошибки которые не нужно логгировать, в данном примере - это ORA-25254):

CONNECT SYSTEM;

DROP TRIGGER log_server_errors;

CREATE OR REPLACE TRIGGER log_server_errors
AFTER SERVERERROR
ON DATABASE WHEN (
     ORA_SERVER_ERROR(1)<>25254 /*ORA-25254: time-out in LISTEN while waiting for a message*/
   )
DECLARE
  v_sql_statement VARCHAR2(32767);
  sql_text ora_name_list_t;
  n        pls_integer;
  cursor c_user is
    select vs.sid                                     sid,
           vs.sql_id                                  sql_id,
           sys_context('USERENV','CLIENT_INFO')       client_info,
           sys_context('USERENV','CURRENT_SCHEMA')    curr_schema,
           sys_context('USERENV','CURRENT_USER')      curr_user,
           sys_context('USERENV','DB_NAME')           db_name,
           sys_context('USERENV','HOST')              host,
           sys_context('USERENV','IP_ADDRESS')        ip,
           sys_context('USERENV','OS_USER')           osuser,
           sys_context('USERENV','SESSIONID')         sessid,
           sys_context('USERENV','SESSION_USER')      sess_user,
           sys_context('USERENV','TERMINAL')          terminal
      from dual
    cross join v$session vs
    where 1 = 1     
      and sys_context('USERENV','SESSIONID') = audsid
  ;
  user_rec c_user%rowtype;
BEGIN
  open c_user;
  fetch c_user into user_rec;
  close c_user;

  n := ora_sql_txt(sql_text);
      
  FOR i IN 1..n LOOP
    v_sql_statement := v_sql_statement || sql_text(i);        
  END LOOP;
  INSERT INTO servererror_log(
    error_datetime, error_user, db_name,
    error_stack, call_stack,
    SID, SQL_ID,
    sql_statement, sql_statement_all,
    CLIENT_INFO, CURR_SCHEMA, CURR_USER, CURR_DB_NAME,
    HOST, IP, OSUSER, SESSID,
    SESS_USER, TERMINAL
  ) VALUES(
    systimestamp, sys.login_user, sys.database_name,
    ora_server_error_msg(1)/*dbms_utility.format_error_stack*/,
    dbms_utility.format_call_stack,
    user_rec.sid, user_rec.sql_id,
    substrb(v_sql_statement, 1, 2000),
    v_sql_statement,
    user_rec.client_info, user_rec.curr_schema, user_rec.curr_user, 
    user_rec.db_name,     user_rec.host,        user_rec.ip, 
    user_rec.osuser,      user_rec.sessid,      user_rec.sess_user, 
    user_rec.terminal
  );
  --commit;
END;
/

Выбираем запросы с ошибками(см. поля SID, SQL_STATEMENT, SQL_STATEMENT_ALL):

CONNECT SCOTT;

select * from system.servererror_log
 --where ERROR_USER = 'SCOTT'
 order by error_datetime desc
;

Дополнительные команды которые возможно пригодятся для трассировки сессии:

CONNECT SYSTEM;

ALTER SYSTEM SET sql_trace = true SCOPE=MEMORY;
...
  select * from system.servererror_log;
...

ALTER SYSTEM SET sql_trace = false SCOPE=MEMORY;

--After Logon Trigger for Tracing on Schema:
CONNECT SCOTT;

CREATE OR REPLACE TRIGGER SCOTT_AFTER_LOGON_TRG_00
  AFTER LOGON ON SCOTT.SCHEMA 
DECLARE
 sqlstr VARCHAR2(200) := 'ALTER SESSION SET EVENTS ''10046 TRACE NAME CONTEXT FOREVER, LEVEL 4''';
BEGIN
  IF (USER = 'SCOTT') THEN
    execute immediate sqlstr;
  END IF;
END;
/