Para melhor visualização, recomendo resolução de no mínimo 1280 x 800 e navegador Mozilla Firefox


sábado, 3 de julho de 2010

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

Por Eduardo Legatti

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.



6 comentários:

Rodrigo Santana disse...

Olá Eduardo, tudo bom?
Muito interessante essa trigger de eventos de sistema. Achei de extrema utilidade principalmente para auditar eventos de usuários conectados como SYS ou SYSTEM. Parabéns pelo artigo, um abraço!

David Siqueira disse...

Achei fantastico Eduardo, gostei muito mesmo do Post. Meus parabéns.
Forte Abraço.
Sucesso Sempre!!!!

Eduardo disse...

Muito útil o código. Conheço muito pouco de oracle, aí precisei coletar informações de erros. Tentei usar a auditoria do oracle, mas não foi muito satisfatório.

Parabéns.

Unknown disse...

Eduardo Boa Tarde,
Cara não sei se passou por esse caso ao usar a function ora_sql_txt, porém ocorre o seguinte, fiz uma trigger (DML) para gravar alguns dados e um deles seria o sql_text que executou tal ação, porém o mesmo esta retornando null na function quando faço um for.

Fiz uma trigger parecida so que para gravar informações de todo ddl que executarem na instancia, mesmo estrutura e funciona mais na before ou after insert or update não esta funcionando.

Este é o trecho.

for each row
declare
v_sql_text ora_name_list_t;
v_sql varchar2(32767);
v_num integer;
begin
v_num := ora_sql_txt(v_sql_text);

for i in 1 .. nvl(v_num,0) loop
v_sql := v_sql || v_sql_text(i);
end loop;

VLW.

Eduardo Legatti disse...

Olá Wender,

Você pode dar uma pesquisada, mas se não me engano a função ora_sql_txt (sql_text) deixou de ser suportada em triggers DML a partir do Oracle 9i (9.2.0.7). Por isso é retornado sempre NULL.

Abraços,

Legatti

Unknown disse...

Eduardo,
Realmente o mesmo não é mais compatível, apenas para Triggers DDL, resolvi com um join na v$session e v$sqlarea.

VLW.

Postagens populares