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