Canalblog
Editer l'article Suivre ce blog Administration + Créer mon blog
Publicité
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
Les erreurs sous Oracle 3 : PL/SQL

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    


Publicité
Publicité
Commentaires
Blog d'un DBA sur le SGBD Oracle et SQL
Publicité
Archives
Blog d'un DBA sur le SGBD Oracle et SQL
  • Blog d'un administrateur de bases de données Oracle sur le SGBD Oracle et sur les langages SQL et PL/SQL. Mon objectif est de vous faire découvrir des subtilités de ce logiciel, des astuces, voir même des surprises :-)
  • Accueil du blog
  • Créer un blog avec CanalBlog
Visiteurs
Depuis la création 339 331
Publicité