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


segunda-feira, 5 de janeiro de 2015

Capturando erros DML/DDL através da opção "Error Logging" disponível no SQL*Plus do Oracle 11g

Por Eduardo Legatti

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.

Google+

6 comentários:

Sandro Costa da Silva disse...

Boa tarde,
Eduardo.

Muito legal seu post, meus parabéns.
Realmente eu não sabia desse recurso que é bem útil.


Abs,

Sandro

Eduardo Legatti disse...

Olá Sandro,

Obrigado pela visita e pelo comentário!

Abraços

Legatti

Anatoli Andrei disse...

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

Eduardo Legatti disse...

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

Luis Santos disse...

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...

Eduardo Legatti disse...

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

Postagens populares