Olá,
No artigo de Julho/2010 eu demonstrei de forma prática, como capturar os erros gerados pelas instruções SQL executadas no banco de dados, fazendo uso do gatilho (trigger) de sistema chamado AFTER SERVERERROR. O objetivo da trigger é capturar quaisquer erros gerados pelas instruções SQL executadas de todas as sessões conectadas no Oracle. O SQL*Plus do Oracle 11g veio com uma inovação um pouco parecida com esse propósito. A diferença é que somente as instruções executadas pela sessão que está executando o SQL*PLus é que será auditada. O parâmetro ERRORLOGGING quando setado para ON, irá criar uma tabela chamada de SPERRORLOG no schema do usuário que está conectado pleo SQL*Plus. Par padrão, o parâmetro é setado para OFF. Segue abaixo uma demonstração na qual irei conectar com o usuário SCOTT.
C:\>sqlplus scott/tiger
SQL*Plus: Release 11.2.0.1.0 Production on Seg Jan 5 08:17:09 2015
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Conectado a:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show errorlogging;
errorlogging is OFF
SQL> set errorlogging ON;
SQL> show errorlogging;
errorlogging is ON TABLE SCOTT.SPERRORLOG
Após setar o parâmetro ERRORLOGGING para ON acima, podemos ver abaixo qua a tabela SPERRORLOG foi criada. Vale a pena salientar que essa tabela ficará criada até que a mesma seja dropada manualmente.
SQL> desc SPERRORLOG
Nome Nulo? Tipo
----------------------------- -------- ------------------------
USERNAME VARCHAR2(256)
TIMESTAMP TIMESTAMP(6)
SCRIPT VARCHAR2(1024)
IDENTIFIER VARCHAR2(256)
MESSAGE CLOB
STATEMENT CLOB
Irei simular a execução de uma instrução SQL e também a execução de um script SQL afim de gerar alguns erros:
SQL> create table t1 (id numberr);
create table t1 (id numberr)
*
ERRO na linha 1:
ORA-00902: tipo de dados inválido
SQL> @script01.sql
select * from t2 where id=1
*
ERRO na linha 1:
ORA-00942: a tabela ou view não existe
Após a execução das instruções SQL acima, poderemos consultar a tabela SPERRORLOG.
SQL> select timestamp,script,message,statement from sperrorlog;
TIMESTAMP SCRIPT MESSAGE STATEMENT
----------------- ------------- -------------------------------------- ----------------------------
05/01/15 08:21:54 ORA-00902: tipo de dados inválido create table t1 (id numberr)
05/01/15 08:22:59 script01.sql ORA-00942: a tabela ou view não existe select * from t2 where id=1
2 linhas selecionadas.
Por fim, vale a pena salientar que os registros inseridos na tabela SPERRORLOG participam da mesma transação da instrução SQL que originou o erro. Portanto, caso um comando de ROLLBACK seja emitido após uma instrução gerar algum erro, a instrução não será persistida na tabela SPERRORLOG.
6 comentários:
Boa tarde,
Eduardo.
Muito legal seu post, meus parabéns.
Realmente eu não sabia desse recurso que é bem útil.
Abs,
Sandro
Olá Sandro,
Obrigado pela visita e pelo comentário!
Abraços
Legatti
Opa Eduardo,
Bem legal o post, mas fiquei com uma dúvida (acho que boba...), mas o usuário conseguirá realizar este set mesmo não tendo privilégios de create table ou quotas?
Abs,
Anatoli
Olá Anatoli,
A dúvida é pertinente. Bom, se o usuário não tiver os privilégios necessários, então o comando SET falhará. O privilégio CREATE TABLE com a respectiva quota ou o privilégio UNLIMITED TABLESPACE serão necessários. Veja que abaixo o usuário SCOTT só tem o privilégio CREATE SESSION.
C:\>sqlplus scott/tiger
SQL*Plus: Release 11.2.0.1.0 Production on Ter Jan 6 17:56:54 2015
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Conectado a:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from session_privs;
PRIVILEGE
------------------------
CREATE SESSION
1 linha selecionada.
SQL> set errorlogging ON;
ERROR:
ORA-01031: privilégios insuficientes
ERROR:
ORA-00942: a tabela ou view não existe
SP2-1518: A tabela de log de erro SPERRORLOG não existe no esquema A
SP2-1507: Tabela, atribuição ou privilégio do log de erro não encontrado ou não acessível
Abraços
Legatti
Por fim, vale a pena salientar que os registros inseridos na tabela SPERRORLOG participam da mesma transação da instrução SQL que originou o erro. Portanto, caso um comando de ROLLBACK seja emitido após uma instrução gerar algum erro, a instrução não será persistida na tabela SPERRORLOG.
Custava nada essa implementação usar autonomous transactions para evitar a perda dos comandos em caso de rollback...
Olá Luis,
De fato, existe um "workaround" para isso que envolve exatamente a criação de uma procedure com AUTONOMOUS TRANSACTION ;-)
Dá uma pesquisada sobre "SQL*Plus error logging - workaround for ROLLBACK issue"
Abraços
Legatti
Postar um comentário