Blog d'un DBA sur le SGBD Oracle et SQL

19 août 2019

les erreurs sous oracle 2 : contraintes d'intégrité, whenever oserror/sqlerror, trigger servererror, fichiers alert.log/trace


Introduction
Cet article est le deuxième d'une série consacrée à la gestion des erreurs sous Oracle.
Les autres articles sont ici :

- Les erreurs sous Oracle 1 : erreurs syntaxiques/sémantiques, utilitaire OERR, table SPERRORLOG, table ERRLOG

Aujourd'hui nous allons regarder quatre choses : 
- les erreurs liées aux contraintes d'intégrité : EXCEPTIONS INTO EXCEPTIONS
- les erreurs dans les scripts SQL : WHENEVER OSERROR et WHENEVER SQLERROR
- les triggers système : CREATE TRIGGER ... SERVERERROR ...
- les fichiers Oracle : fichier ALERT_<SID>.log et les fichiers de traces


 

Points d'attention
Aucun.
 


 
Base de tests
Une base Oracle 12.


 
Exemples
============================================================================================
Les erreurs liées aux contraintes d'intégrité : EXCEPTIONS INTO EXCEPTIONS

============================================================================================
Oracle permet de stocker dans une table les enregistrements qui sont en erreurs lorsqu'on manipule les contraintes d'intégrité. ATTENTION, cela ne concerne que les enregistrements existant déjà dans une table et non pas ceux qui sont bloqués lors d'un INSERT en violant ces contraintes.

Vous remarquerez qu'Oracle utilise deux fois le terme EXCEPTIONS pour deux objets différents : une table et une erreur liée à une contrainte d'intégrité. Si cette table, de nom EXCEPTIONS, n'existe pas déjà dans le dictionnaire de données, il faut alors la créer.
     SQL> SELECT owner FROM dba_objects WHERE object_name = 'EXCEPTIONS';
     no rows selected
     
ATTENTION : la table EXCEPTIONS a une colonne ROW_ID, de type ROWID, qui stocke les ROWID des enregistrements en erreurs; il y a un _ (underscore) entre ROW et ID.
Les noms des colonnes sont fixés par Oracle.
     SQL> CREATE TABLE EXCEPTIONS(ROW_ID ROWID, OWNER VARCHAR2(30), TABLE_NAME VARCHAR2(30), CONSTRAINT VARCHAR2(30));
     Table created.
     
     SQL> SELECT owner FROM dba_objects WHERE object_name = 'EXCEPTIONS';
     OWNER
     -----------
     HR
     
On crée une table ZZ_TEST avec une contrainte de type CHECK sur la longueur et on insère des données dans cette table.
     SQL> CREATE TABLE ZZ_TEST(ID NUMBER, NAME VARCHAR2(30 CHAR) CONSTRAINT ZZ_TEST_CHECK_NAME CHECK (LENGTH(NAME) >= 10) );
     Table created.
     
     SQL> BEGIN
     FOR i IN 1..100 LOOP
     INSERT INTO ZZ_TEST VALUES(i, 'TEST-TEST' || TO_CHAR(i));
     END LOOP;
     COMMIT;
     END;
     /
     PL/SQL procedure successfully completed.
     
Aucune violation de la contrainte CHECK enregistrée pour le moment.
     SQL> SELECT COUNT(*) FROM EXCEPTIONS;
       COUNT(*)
     ----------
          0
     
On insère un enregistrement violant la contrainte CHECK.
     SQL> INSERT INTO ZZ_TEST VALUES(200, 'TEST-200');
     ORA-02290: check constraint (HR.ZZ_TEST_CHECK_NAME) violated
     
Rien dans la table EXCEPTIONS : normal car l'enregistrement n'ayant pas pu être créé dans la table, il n'existe pas dans celle-ci donc on ne peut rien enregistrer dans la table EXCEPTIONS qui réclame un ROWID.
     SQL> SELECT * FROM EXCEPTIONS;
     no rows selected
     
Maintenant on désactive la contrainte CHECK et on insère des données dans cette table violant la contrainte.
     SQL> ALTER TABLE HR.ZZ_TEST DISABLE CONSTRAINT ZZ_TEST_CHECK_NAME;
     Table altered.
     
     SQL> INSERT INTO ZZ_TEST VALUES(200, 'TEST-200');
     1 row created.
     
     SQL> INSERT INTO ZZ_TEST VALUES(201, 'TEST-TEST201');
     1 row created.
     
     SQL> INSERT INTO ZZ_TEST VALUES(202, 'TEST-202');
     1 row created.
     
     SQL> INSERT INTO ZZ_TEST VALUES(203, 'TEST-TEST203');
     1 row created.
     
     SQL> commit;
     Commit complete.
     
En réactivant la contrainte avec l'option EXCEPTIONS INTO EXCEPTIONS, les enregistrements en erreur de la table de base sont enregistrés dans la table EXCEPTIONS.
     SQL> ALTER TABLE HR.ZZ_TEST ENABLE CONSTRAINT ZZ_TEST_CHECK_NAME EXCEPTIONS INTO EXCEPTIONS;
     ALTER TABLE HR.ZZ_TEST ENABLE CONSTRAINT ZZ_TEST_CHECK_NAME EXCEPTIONS INTO EXCEPTIONS
                                              *
     ERROR at line 1:
     ORA-02293: cannot validate (HR.ZZ_TEST_CHECK_NAME) - check constraint violated
     
     SQL> SELECT * FROM EXCEPTIONS;
     ROW_ID           OWNER              TABLE_NAME             CONSTRAINT
     ------------------ ------------------------------ --------------------------
     AAAT7lAAMAAACWDABk HR                  ZZ_TEST             ZZ_TEST_CHECK_NAME
     AAAT7lAAMAAACWDABm HR                  ZZ_TEST             ZZ_TEST_CHECK_NAME
     
Pour des infos plus lisibles, c'est à dire identifier quels enregistrements de la table de base posent problème, on joint la table EXCEPTIONS avec la table de base. ATTENTION au piège : je rappelle qu'il faut utiliser pour la table EXCEPTIONS la colonne ROW_ID et pas la colonne ROWID sinon l'ordre ne renvoit rien :-)
     SQL> SELECT E.*, T.* FROM EXCEPTIONS E, ZZ_TEST T WHERE E.ROW_ID = T.ROWID order by T.id;
     ROW_ID                   OWNER    TABLE_NAME    CONSTRAINT            ID NAME
     ------------------ ------------------------------ ----------------------
     AAAT7lAAMAAACWDABk     HR        ZZ_TEST        ZZ_TEST_CHECK_NAME    200 TEST-200
     AAAT7lAAMAAACWDABm     HR        ZZ_TEST        ZZ_TEST_CHECK_NAME    202 TEST-202
     
     SQL> SELECT E.*, T.* FROM EXCEPTIONS E, ZZ_TEST T WHERE E.ROWID = T.ROWID order by T.id;
     no rows selected
     
Cette fonctionnalité fonctionne aussi pour les UPDATEs et DELETEs : à vous de tester!
    
Pour info, les erreurs liées à la modification de la structure de la table ne sont pas gérées. Par exemple si on diminue la taille d'une colonne alors que des enregistrements ont l'ancienne taille max, on ne peut pas utiliser l'option ALTER TABLE MODIFY ... EXCEPTIONS INTO EXCEPTIONS.
Exemple : passer une colonne VARCHAR2(60) à VARCHAR2(20).
     SQL> CREATE TABLE ZZZ_TEST_DDL (id NUMBER, LIB VARCHAR2(60 CHAR));
     Table created.
     
     SQL> INSERT INTO ZZZ_TEST_DDL VALUES (1, '012345678901234567890123456789012345678901234567890123456789');
     1 row created.
     
     SQL> commit;
     Commit complete.
     
     SQL> ALTER TABLE ZZZ_TEST_DDL MODIFY (LIB VARCHAR2(20)) EXCEPTIONS INTO EXCEPTIONS;
     ALTER TABLE ZZZ_TEST_DDL MODIFY (LIB VARCHAR2(20)) EXCEPTIONS INTO EXCEPTIONS
                                                        *
     ERROR at line 1:
     ORA-01735: invalid ALTER TABLE option


============================================================================================
Les erreurs dans les scripts SQL : WHENEVER OSERROR et WHENEVER SQLERROR

============================================================================================

Dans un script SQL, il faut mettre les deux lignes ci-dessous pour propager au script appelant le ou les codes erreurs rencontrés lors de l'exécution du fichier sql. Les options disant quoi faire sont nombreuses, je vous laisse regarder la doc officielle :-)
     - WHENEVER SQLERROR : "performs the specified action (exits SQL*Plus by default) if a SQL command or PL/SQL block generates an error."
     - WHENEVER OSERROR : "performs the specified action (exits SQL*Plus by default) if an operating system error occurs (such as a file writing error)."
    
Contenu du fichier de test zztest.sql : on utilise une table inexistante.
     WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK
     WHENEVER OSERROR EXIT SQL.SQLCODE ROLLBACK
     SELECT * FROM TEST999;
     
Sous SQL*Plus voici ce qui arrive quand je lance directement zztest.sql : je suis déconnecté de la base et je sors de SQL*Plus.
     SQL> @zztest.sql
     SELECT * FROM TEST999
                   *
     ERROR at line 1:
     ORA-00942: table or view does not exist
     Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
     [oracle@vbgeneric ~]$
     
Maintenant je crée un script Linux .sh qui appelle mon .sql et qui gère l'erreur rencontrée. Mon script est basique, mon expertise est sur l'administration des bases Oracle, pas sur le scripting Linux :-)
Contenu du fichier zztest.sh.
     sqlplus "HR/HR" @zztest.sql
     echo Returned $?
     
Exécution du .sh : le code erreur SQLCODE vaut 174 et il est bien affiché!
     [oracle@vbgeneric ~]$ . zztest.sh
     SQL*Plus: Release 12.2.0.1.0 Production on Fri Aug 16 09:09:43 2019
     Copyright (c) 1982, 2016, Oracle.  All rights reserved.
     Last Successful login time: Fri Aug 16 2019 09:05:42 -04:00
     Connected to:
     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
     
     SELECT * FROM TEST999
                   *
     ERROR at line 1:
     ORA-00942: table or view does not exist
     
     Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
     Returned 174
     [oracle@vbgeneric ~]$


============================================================================================
Trigger sur SERVERERROR

============================================================================================

Sous Oracle, il est possible de créer des triggers sur des évènements système. L'un de ceux-ci est SERVERERROR, qui est déclenché quand une erreur Oracle survient. Voici un lien avec la liste des évènements système et clients peuvant être traités par un trigger, avec en plus la liste des variables associées à chaque trigger : https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/triggers.htm#g1722272

ATTENTION, il est précisé que toutes les erreurs ne sont pas interceptées par ce trigger "The trigger does not fire on ORA-1034, ORA-1403, ORA-1422, ORA-1423, and ORA-4030 because they are not true errors or are too serious to continue processing. It also fails to fire on ORA-18 and ORA-20 because a process is not available to connect to the database to record the error."
    
Pour Oracle V11, voici quels sont les variables d'environnement pour le trigger SERVERERROR :
     - ora_sysevent
     - ora_login_user
     - ora_instance_num
     - ora_database_name
     - ora_server_error
     - ora_is_servererror
     - space_error_info
    
Pour cette partie, j'ai quasiment recopié un test de René Nyffenegger trouvé ici : http://www.adp-gmbh.ch/ora/sql/trigger/servererror.html
     SQL> show user
     USER is "SYS"
     
     SQL> create table SYSTEM.caught_errors (
         dt        date,               
         username  varchar2(30 CHAR),
         msg       varchar2(512 CHAR),
         stmt      varchar2(512 CHAR),
         v_ora_sysevent varchar2( 100 CHAR),
         v_ora_login_user varchar2( 100 CHAR),
         v_ora_instance_num varchar2( 100 CHAR),
         v_ora_database_name varchar2( 100 CHAR),
         v_ora_server_error varchar2( 100 CHAR)
     );
     Table created.
     
Le trigger suivant va intercepter toute erreur sur la base. Pour traiter seulement les erreurs du schéma courant, il faut remplacer "servererror on database" par "servererror on schema"
     SQL> create or replace trigger catch_errors after servererror on database
     declare
         sql_text ora_name_list_t; -- ora_name_list_t is defined in package DBMS_STANDARD as TYPE ora_name_list_t IS TABLE OF VARCHAR2(64);
         msg_     varchar2(2000) := null;
         stmt_    varchar2(2000) := null;
     
     begin
         -- On récupère toutes les erreurs
         for depth in 1 .. ora_server_error_depth loop -- ora_server_error_depth : returns the total number of error messages on the error stack
             msg_ := msg_ || ora_server_error_msg(depth); -- ora_server_error_msg : given a position (1 for top of stack), it returns the error message at that position on error stack
         end loop;
     
         for i in 1 .. ora_sql_txt(sql_text) loop -- ora_sql_txt : returns the SQL text of the triggering statement in the OUT parameter. If the statement is long, it is broken into multiple PL/SQL table elements.
             stmt_ := stmt_ || sql_text(i);
         end loop;
     
         insert into SYSTEM.caught_errors values (sysdate, ora_login_user, msg_, stmt_, ora_sysevent, ora_login_user, ora_instance_num, ora_database_name, ora_server_error(1));
     end;
     /
     Trigger created.
     
Maintenant, on provoque une erreur : elle est bien enregistrée dans ma table.
     SQL> select * from SYSTEM.zz01;
     select * from SYSTEM.zz01
                          *
     ERROR at line 1:
     ORA-00942: table or view does not exist
     
     SQL> select * from SYSTEM.caught_errors;
     DT  USERNAME  MSG  STMT    V_ORA_SYSEVENT    V_ORA_LOGI V_ORA_INST     V_ORA_DATA     V_ORA_SERV
     --------- ---------- ---------------------------------------- ------------------------------
     19-AUG-19 SYS  ORA-00942: table or view does not exist  select * from SYSTEM.zz01  SERVERERROR  SYS  1  ORCL  942
    
Et si on provoque une autre erreur, celle-ci n'écrase pas l'erreur précédente. On peut donc, avec ce système de trigger, avoir l'historique des erreurs survenues sur notre base.
     SQL> select dba_roles;
     select dba_roles
                    *
     ERROR at line 1:
     ORA-00923: FROM keyword not found where expected
    
     SQL> select * from SYSTEM.caught_errors;
     DT            USERNAME       MSG                                              STMT                         V_ORA_SYSEVENT        V_ORA_LOGI V_ORA_INST V_ORA_DATA V_ORA_SERV
     --------- ---------- ---------------------------------------- ------------------------------
     19-AUG-19 SYS  ORA-00942: table or view does not exist  select * from SYSTEM.zz01 SERVERERROR  SYS  1 ORCL  942
     19-AUG-19 SYS  ORA-00923: FROM keyword not found where  select dba_roles          SERVERERROR  SYS  1 ORCL  923
                               expected

============================================================================================
Les fichiers ALERT_<SID>.log et de traces

============================================================================================

Le fichier d'alertes d'Oracle est le premier endroit à lire si il y a un problème sur votre base. Ici, Oracle écrit les erreurs sévères de votre base : problèmes sur la modification de sa structure, deadlock détecté etc etc. Pour avoir ses emplacements, il faut interroger V$DIAG_INFO.
     SQL> select * from v$diag_info;
     INST_ID     NAME     VALUE    CON_ID
     -----------------------------------
     ...
          1 Diag Trace /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace     0 -- ALERT_<SID>.log
          1 Diag Alert /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/alert     0 -- ALERT_<SID>.xml
     ...
     
Quelques exemples d'erreur dans le fichier ALERT.
     SQL> ! ls -l /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace/*log
     -rw-r----- 1 oracle oinstall 1942554 Aug 19 03:42 /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace/alert_orcl12c.log
     
Parfois Oracle nous donne le nom d'un fichier trace qui est censé contenir plus d'informations que ce qui est marqué dans le fichier ALERT.
     2017-03-02T07:59:02.700434-05:00
     Errors in file /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace/orcl12c_q003_10267.trc:
     ORA-01023: Cursor context not found (Invalid cursor number)
     ORA-00448: normal completion of background process
     
     2017-03-02T08:34:22.347536-05:00
     ORCL(3):Errors in file /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace/orcl12c_s000_12853.trc:
     ORA-00942: table or view does not exist
     
     2018-08-18T10:02:36.198661-04:00
     Errors in file /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace/orcl12c_j000_6231.trc:
     ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_21"
     ORA-20001: Statistics Advisor: Invalid task name for the current user
     ORA-06512: at "SYS.DBMS_STATS", line 47207
     ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 882
     ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 20059
     ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 22201
     ORA-06512: at "SYS.DBMS_STATS", line 47197
     
Et voici le contenu du fichier de traces pour la dernière erreur ci-dessus.
     Linux> view /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace/orcl12c_j000_6231.trc    
     Trace file /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace/orcl12c_j000_6231.trc
     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
     Build label:    RDBMS_12.2.0.1.0_LINUX.X64_170125
     ORACLE_HOME:    /u01/app/oracle/product/12.2/db_1
     System name:    Linux
     Node name:      vbgeneric
     Release:        4.1.12-61.1.27.el7uek.x86_64
     Version:        #2 SMP Fri Feb 3 12:31:56 PST 2017
     Machine:        x86_64
     Instance name: orcl12c
     Redo thread mounted by this instance: 1
     Oracle process number: 78
     Unix process pid: 6231, image: oracle@vbgeneric (J000)
     
     
     *** 2018-08-18T10:02:36.198441-04:00 (CDB$ROOT(1))
     *** SESSION ID:(88.21243) 2018-08-18T10:02:36.198474-04:00
     *** CLIENT ID:() 2018-08-18T10:02:36.198479-04:00
     *** SERVICE NAME:(SYS$USERS) 2018-08-18T10:02:36.198483-04:00
     *** MODULE NAME:(DBMS_SCHEDULER) 2018-08-18T10:02:36.198487-04:00
     *** ACTION NAME:(ORA$AT_OS_OPT_SY_21) 2018-08-18T10:02:36.198491-04:00
     *** CLIENT DRIVER:() 2018-08-18T10:02:36.198494-04:00
     *** CONTAINER ID:(1) 2018-08-18T10:02:36.198498-04:00
     
     ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_21"
     ORA-20001: Statistics Advisor: Invalid task name for the current user
     ORA-06512: at "SYS.DBMS_STATS", line 47207
     ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 882
     ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 20059
     ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 22201
     ORA-06512: at "SYS.DBMS_STATS", line 47197    


Posté par David DBA à 15:31 - - Permalien [#]
Tags : ,


09 août 2019

Les erreurs sous Oracle 1 : erreurs syntaxiques/sémantiques, utilitaire OERR, table SPERRORLOG, table ERRLOG


Introduction
Cet article est le premier d'une série consacrée à la gestion des erreurs sous Oracle. Ce sujet est vaste, ces erreurs pouvant être gérées sous SQL*Plus, en PL/SQL, dans des fichiers, via des triggers, j'en passe et des meilleurs :-)  Attention, le but ici est de présenter cette gestion des erreurs, pas d'en faire une analyse poussée et exhaustive, ça, c'est à vous de le faire selon vos besoins...

Les autres articles sont ici : 
les erreurs sous oracle 2 : contraintes d'intégrité, whenever oserror/sqlerror, trigger servererror, fichiers alert.log/trace


Aujourd'hui nous allons regarder quatre choses : 
- les erreurs syntaxiques et sémantiques
- l'utilitaire Linux OERR
- tracer les erreurs SQL : la table SPERRORLOG
- la tolérance aux erreurs DML : package DBMS_ERRLOG, table ERRLOG et clause LIMIT
 


 

Points d'attention
Aucun.
 


 
Base de tests
Une base Oracle 12.


 
Exemples
============================================================================================
Les erreurs syntaxiques et sémantiques

============================================================================================
Il existe deux grand types d'erreur sous Oracle, les erreurs syntaxiques et les erreurs sémantiques. Malheureusement il n'est pas possible de les distinguer via un code! Si le besoin existe, alors il faut lister les erreurs les plus fréquentes des deux types et faire ensuite une gestion numéro d'erreur par numéro d'erreur; dans un programme PL/SQL par exemple.

Erreur syntaxique : c'est quand un ordre SQL est mal écrit. Ici il n'y a aucun nom de colonne ni le caractère *. 
     SQL> SELECT FROM test444;
     SELECT FROM test444
     *
     ERROR at line 1:
     ORA-00936: missing expression

Exemple d'erreur sémantique : la syntaxe est bonne mais il y a un pb avec les objets de la requête ou du user qui la lance; ici la table ou vue n'existe pas.
     SQL> select * from test444;
     select * from test444
     *
     ERROR at line 1:
     ORA-00942: table or view does not exist


============================================================================================
L'utilitaire Linux OERR

============================================================================================

Quand une erreur survient, un message est affiché par SQL*Plus.
     SQL> select * frommm;
     select * frommm
              *
     ERREUR a la ligne 1 :
     ORA-00923: mot-cle FROM absent a l'emplacement prevu
      
On peut parfois avoir plus d'information sur celle-ci en utilisant l'utilitaire Linux OERR; il a en effet deux champs appelés "Cause" et "Action" en plus. Cet outil extrait les messages d'erreur depuis un fichier Oracle; ce fichier peut-être mis à jour manuellement pour avoir des messages plus précis mais c'est hors scope de cet article.
     SQL> ! oerr
     Usage: oerr facility error
      
     Facility is identified by the prefix string in the error message.
     For example, if you get ORA-7300, "ora" is the facility and "7300"
     is the error.  So you should type "oerr ora 7300".
      
     If you get LCD-111, type "oerr lcd 111", and so on.
      
Parfois on n'a aucune info en plus de celle affichée par SQL*Plus; ah si, dans mon cas le texte est en anglais, ce qui en général est bien plus utile (avis personnel) :-)
     SQL> ! oerr ora 923
     00923, 00000, "FROM keyword not found where expected"
     // *Cause:
     // *Action:
      
Mais, parfois, selon le code erreur, les champs Cause et Action peuvent être renseignés et on a alors plus d'info que le message par défaut de SQL*Plus.
     SQL> ! oerr ora 42
     00042, 0000, "Unknown Service name %s"
     // *Cause:    An attempt was made to use an invalid application service.
     // *Action:   Use a valid service name from SERVICE$ or add a new
     //            service using the DBMS_SERVICE package.


============================================================================================
Tracer les erreurs SQL : la table SPERRORLOG

============================================================================================

Il est possible de tracer les erreurs survenues sous SQL*Plus dans une table dédiée, appelée SPERRORLOG. Les erreurs tracées sont les erreurs SQL mais aussi SQL*Plus. Au préalable, il faut activer cette fonctionnalité avec le paramètre errorlogging ("enregistrements des erreurs" en français). Cela a pour effet de créer une table SPERRORLOG dans le schéma courant.
     
Voici les options de la commande sous SQL*Plus.
     SQL> help set
     ...
     ERRORL[OGGING] {ON|OFF} [TABLE [schema.]tablename] [TRUNCATE] [IDENTIFIER identifier]  
     ...
     
     SQL> set errorlogging on
     
     SQL> show user
     USER is "HR"
     
     SQL> select owner, object_type from dba_objects where object_name= 'SPERRORLOG';
     OWNER        OBJECT_TYPE
     -----------------------
     HR            TABLE
     
     SQL> desc SPERRORLOG
     Name                       Null?    Type
     ----------------------------------------- -------- ----------------------------
     USERNAME                        VARCHAR2(256)
     TIMESTAMP                        TIMESTAMP(6)
     SCRIPT                             CLOB
     IDENTIFIER                        VARCHAR2(256)
     MESSAGE                            CLOB
     STATEMENT                        CLOB
     
On va tester avec deux types d'erreur.
Erreur SQL : SELECT qui provoque une erreur.
     SQL> select * from test99;
     select * from test99
                   *
     ERROR at line 1:
     ORA-00942: table or view does not exist
     
Erreur SQL*Plus.
     SQL> col STATEMENT col A30
     SP2-0158: unknown COLUMN option "col"

OK, les messages d'erreurs sont bien sauvegardés dans la nouvelle table.
     SQL> SELECT * from SPERRORLOG order by TIMESTAMP;
     USERNAME       TIMESTAMP          SCRIPT    IDENTIFIER MESSAGE                     STATEMENT
     ---------- ------------------------------ ------------------- ---------- ----------------------
     HR     08-AUG-19 01.26.14.000000 PM   ORA-00942: table or view does not exist      select * from test99
     HR     08-AUG-19 01.29.29.000000 PM   SP2-0158: unknown COLUMN option "col"        col STATEMENT col A30
     
A noter que même si je ferme ma session, le contenu de la table SPERRORLOG n'est pas supprimé; ouf, cela permet d'avoir un historique des erreurs :-)
     SQL> exit
     Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
     
     [oracle@vbgeneric ~]$ sqlplus HR
     Enter password:
     
     SQL> select TIMESTAMP, MESSAGE, STATEMENT from SPERRORLOG order by TIMESTAMP;
     TIMESTAMP                           MESSAGE                                        STATEMENT
     ------------------------------ ---------------------------------------- ----------------------------------------
     08-AUG-19 01.26.14.000000 PM       ORA-00942: table or view does not exist        select * from test99
     08-AUG-19 01.29.29.000000 PM       SP2-0158: unknown COLUMN option "col"        col STATEMENT col A30
     
On peut aussi créer sa propre table d'erreurs, pour éviter que la table SPERRORLOG ne soit purgée, par un batch de DBA par exemple.
     SQL> create table my_sperror_log
         (
            username varchar2(256),
            timestamp       timestamp,
            script          clob,
            identifier      varchar(256),
            message         clob,
            statement       clob
         );
     Table created.
     
Avec l'option "errorlogging on table", je donne le nom d'une table pour tracer les erreurs et on voit bien que SPERRORLOG n'est plus mise à jour.
     SQL> set errorlogging on table my_sperror_log;
     
     SQL> select * from test 55555;
     select * from test 55555
                        *
     ERROR at line 1:
     ORA-00933: SQL command not properly ended
     
     SQL> select TIMESTAMP, MESSAGE, STATEMENT from SPERRORLOG order by TIMESTAMP;
     TIMESTAMP                           MESSAGE                                        STATEMENT
     ------------------------------ ---------------------------------------- ----------------------------------------
     08-AUG-19 01.26.14.000000 PM       ORA-00942: table or view does not exist        select * from test99
     08-AUG-19 01.29.29.000000 PM       SP2-0158: unknown COLUMN option "col"        col STATEMENT col A30
     
     SQL> select TIMESTAMP, MESSAGE, STATEMENT from my_sperror_log order by TIMESTAMP;
     TIMESTAMP                           MESSAGE                                        STATEMENT
     ------------------------------ ----------------------------------------     -------------------------
     08-AUG-19 01.40.06.000000 PM       ORA-00933: SQL command not properly ended     select * from test 55555
    
Si on vide la table des erreurs sans préciser un nom, c'est la tablecourante qui est vidée et pas forcément SPERRORLOG.
     SQL> set errorlogging on truncate
     
     SQL> select count(*) from SPERRORLOG;         
       COUNT(*)
     ----------
          2
     
     SQL> select count(*) from my_sperror_log;
       COUNT(*)
     ----------
          0
     
L'option IDENTIFIER quant à elle permet d'attribuer un id à chaque session dans la table des erreurs.Ainsi on pourra filtrer les erreurs sur un user. Attention, pas de quotes ou guillemets pour le libellé de l'identifiant.
     SQL> set errorlogging on identifier DAVID;
     SQL>
     
     SQL> SELECT * FROM cvfg;
     SELECT * FROM cvfg
                   *
     ERROR at line 1:
     ORA-00942: table or view does not exist
     
     SQL> select TIMESTAMP, IDENTIFIER, MESSAGE, STATEMENT from my_sperror_log where identifier = 'DAVID' order by TIMESTAMP;
     TIMESTAMP                      IDENTIFIER    MESSAGE                                   STATEMENT
     ------------------------------ -------------------- ------------------------------------------------------
     08-AUG-19 01.50.03.000000 PM   DAVID         ORA-00942: table or view does not exist   SELECT * FROM cvfg
     
J'ouvre maintenant une session avec le user SYS et je renouvelle l'expérience, en utilisant bien comme table des erreurs celle du user HR sinon Oracle va me créer dans le schéma SYS une table SPERRORLOG.
     SQL> connect SYS as sysdba
     SQL> set errorlogging on table HR.my_sperror_log;
     SQL> set errorlogging on identifier SYS;
     
Je provoque une erreur comme SYS. On voit bien ensuite qu'on peut filtrer les erreurs selon l'utilisateur qui a provoqué les erreurs. Il est alors facile de créer un "TRIGGER AFTER LOGON" et de renseigner pour chaque user connecté le paramètre IDENTIFIER avec le USERNAME de connexion. 
     SQL> set columnzzzz NAME for A30
     SP2-0158: unknown SET option "columnzzzz"
     
     SQL> select TIMESTAMP, IDENTIFIER, MESSAGE, STATEMENT from HR.my_sperror_log order by TIMESTAMP;
     TIMESTAMP                      IDENTIFIER   MESSAGE                                         STATEMENT
     ------------------------------ -------------------- ---------------------------------------- -------------------
     08-AUG-19 01.50.03.000000 PM   DAVID        ORA-00942: table or view does not exist      SELECT * FROM cvfg
     08-AUG-19 01.55.54.000000 PM   SYS          SP2-0158: unknown SET option "columnzzzz"      set columnzzzz NAME for A30


============================================================================================
La tolérance aux erreurs DML : package DBMS_ERRLOG, table ERRLOG et clause LIMIT

============================================================================================

Dans la syntaxe du INSERT, UPDATE, DELETE, MERGE depuis Oracle 10g, il est possible de logger les erreurs des opérations DML de façon détaillées (avec le code et le message Oracle) et même de bypasser les erreurs pour éviter qu'un programme ne s'arrête et n'annule tout ce qui s'est bien déroulé avant.

Création de la table de tests avec 100 enregistrements.
     SQL> CREATE TABLE ZZZ_DDU(ID NUMBER(7) NOT NULL, LIB VARCHAR2(20 CHAR));
      
     SQL> BEGIN
               FOR i in 1..100
                    LOOP
                         INSERT INTO ZZZ_DDU VALUES(i, TO_CHAR(i));
               END LOOP;
               COMMIT;
     END;
     /
      
Création de la table stockant les erreurs avec le package DBMS_ERRLOG pour la table ZZZ_DDU.
Voici la définition de ce package dans la doc Oracle; à noter qu'il ne contient qu'une seule procédure.
"The DBMS_ERRLOG package provides a procedure that enables you to create an error logging table so that DML operations can continue after encountering errors rather than abort and roll back. This enables you to save time and system resources."
     SQL> EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('ZZZ_DDU', 'errlog');
      
Test avec N Insert qui plantent mais sans la clause REJECT LIMIT : seule la première erreur est tracée car le programme s'arrête de suite.
     SQL> BEGIN
               FOR i in 1000..1010
               LOOP
                    INSERT INTO ZZZ_DDU VALUES(i, DECODE(MOD(i, 2), 0, to_char(i), RPAD('A', 50, 'X'))) LOG ERRORS INTO ERRLOG ('TEST ZZZ_DDU');
               END LOOP;
               COMMIT;
     END;
     /
      
     BEGIN
     *
     ERREUR a la ligne 1 :
     ORA-12899: valeur trop grande pour la colonne "ZZTEST"."ZZZ_DDU"."LIB" (reelle : 50, maximum : 20)
     ORA-06512: a ligne 4
      
     SQL> SELECT * FROM ERRLOG;
     ORA_ERR_NUMBER$ ORA_ERR_MESG$ ORA_ERR_RO OR ORA_ERR_TAG$    ID    LIB
     --------------- -------------------------------------------------- ---------- -- --------------- -----
     12899 ORA-12899: valeur trop grande pour la colonne "ZZT  I  TEST ZZZ_DDU    1001  AXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
     EST"."ZZZ_DDU"."LIB" (reelle : 50, maximum : 20)                                       XXXXXXXXXXXXXXXXXXXX
      
Test avec 11 INSERTs qui plantent avec la clause REJECT LIMIT à 50 : toutes les erreurs sont bien logguées et Oracle continue à traiter les INSERTs tant qu'il n'arrive pas à 50 erreurs. On ne constate d'ailleurs aucun message d’erreur dans SQL*Plus sur cette opération.
     SQL> truncate table errlog;
     SQL> truncate table ZZZ_DDU;
     SQL> BEGIN
               FOR i in 1000..1010
               LOOP
                    INSERT INTO ZZZ_DDU VALUES(i, DECODE(MOD(i, 2), 0, to_char(i), RPAD('A', 50, 'X'))) LOG ERRORS INTO ERRLOG ('TEST ZZZ_DDU') REJECT LIMIT 50;
               END LOOP;
               COMMIT;
          END;
          /
    
Toutes les erreurs sont logguées dans la table des erreurs dans la limite du REJECT LIMIT.
     SQL> SELECT ORA_ERR_NUMBER$, ORA_ERR_TAG$, ID FROM ERRLOG order by ID;
     ORA_ERR_NUMBER$ ORA_ERR_TAG$    ID
     --------------- --------------- -----
               12899 TEST ZZZ_DDU    1001
               12899 TEST ZZZ_DDU    1003
               12899 TEST ZZZ_DDU    1005
               12899 TEST ZZZ_DDU    1007
               12899 TEST ZZZ_DDU    1009
     
Tous les INSERTsOK sont bien dans la table ZZZ_DDU, ils n’ont pas été annulés par le premier INSERT en erreur puisqu'on tolère 50 erreurs avant qu'un ROLLBACK ne soit déclenché.
     SQL> SELECT * FROM ZZZ_DDU order by ID;
             ID LIB
     ---------- ------------------------------
     ########## 1000
     ########## 1002
     ########## 1004
     ########## 1006
     ########## 1008
     ########## 1010
      
     6 lignes selectionnees.



Posté par David DBA à 11:07 - - Permalien [#]
Tags : ,