Capturando erros DML/DDL gerados pelas sessões: Um pouco da trigger de sistema AFTER SERVERERROR

Olá,

Para quem é aficionado em auditoria de banco de dados, com certeza já deve ter ouvido falar do gatilho de evento de sistema chamado SERVERERROR. Esta trigger de sistema é capaz de capturar erros gerados pelas instruções SQL executadas na sessões que estão atualmente conectadas no banco de dados Oracle. Caso, por algum motivo, queiramos armazenar a data, a sentença SQL e o erro gerado por ela, poderemos armazená-los em uma tabela específica para este propósito. Abaixo está um exemplo prático de como poderemos fazer isso.

C:\>sqlplus system/*******

SQL*Plus: Release 10.2.0.1.0 - Production on Sáb Jul 3 21:18:20 2010

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

Conectado a:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

-- Irei criar uma tablespace específica para a tabela
SQL> create tablespace tbs_erros_sql
  2  logging
  3  datafile 'c:\oraclexe\oradata\xe\erros_sql.dbf' size 100M
  4  extent management local
  5  segment space management auto;

Tablespace criado.

-- Irei criar a tabela que irá armazenar os registros com os erros
SQL> create table erros_sql (
  2  data date,
  3  usuario varchar2(30),
  4  msg_erro varchar2(4000),
  5  stmt_erro varchar2(4000)
  6  ) tablespace tbs_erros_sql;

Tabela criada.

-- Irei criar a trigger de sistema que irá capturar os erros gerados pela sessões
SQL> create or replace trigger trg_captura_erros
  2  after servererror on database
  3  declare
  4     sql_text ora_name_list_t;
  5     msg_erro     varchar2(4000) := null;
  6     stmt_erro    varchar2(4000) := null;
  7  begin
  8    for depth in 1 .. ora_server_error_depth loop
  9      msg_erro := msg_erro || ora_server_error_msg(depth);
 10    end loop;
 11    for i in 1 .. ora_sql_txt(sql_text) loop
 12       stmt_erro := stmt_erro || sql_text(i);
 13    end loop;
 14    insert into erros_sql
 15      values (sysdate,ora_login_user,msg_erro,stmt_erro);
 16  end;
 17  /

Gatilho criado.

SQL> grant select on erros_sql to public;

Concessão bem-sucedida.

SQL> create public synonym erros_sql for system.erros_sql;

Sinônimo criado.

Bom, após criação dos objetos acima, irei realizar abaixo alguns testes de forma a simular erros de DML e DDL gerados pelas sessões conectadas no banco de dados.

SQL> connect scott/tiger
Conectado.

SQL> drop table teste;
drop table teste
           *
ERRO na linha 1:
ORA-00942: a tabela ou view não existe

SQL> select sys_date from dual;
select sys_date from dual
       *
ERRO na linha 1:
ORA-00904: "SYS_DATE": identificador inválido

SQL> connect adam/adam
Conectado.

SQL> create table pai (id number constraint pk_pai primary key);

Tabela criada.

SQL> insert into pai (1);
insert into pai (1)
                 *
ERRO na linha 1:
ORA-00928: palavra-chave SELECT não encontrada

SQL> insert into pai values (1);

1 linha criada.

SQL> insert into pai values (1);
insert into pai values (1)
*
ERRO na linha 1:
ORA-00001: restrição exclusiva (ADAM.PK_PAI) violada

Pronto. Após a simulação acima, poderemos verificar abaixo as informações na tabela ERROS_SQL.

SQL> select  from erros_sql order by data;

DATA       USUARIO MSG_ERRO                                              STMT_ERRO
---------- ------- ---------------------------------------------------- --------------------------
03/07/2010 SCOTT   ORA-00942: a tabela ou view não existe               drop table teste
03/07/2010 SCOTT   ORA-00904: "SYS_DATE": identificador inválido        select sys_date from dual
03/07/2010 ADAM    ORA-00928: palavra-chave SELECT não encontrada       insert into pai (1)
03/07/2010 ADAM    ORA-00001: restrição exclusiva (ADAM.PK_PAI) violada insert into pai values (1)

4 linhas selecionadas.

No mais, vale a pena salientar que a trigger de sistema SERVERERROR também captura erros gerados pelos usuários SYSTEM e SYS.