FOREIGN KEY et ORA-00054

Un développeur m’a demandé de l’aide aujourd’hui car il ne comprenait pas pourquoi il obtenait l’erreur ORA-00054 alors qu’il souhaitait simplement créer une nouvelle table:

SQL> CREATE TABLE ETF_TRACKING_ERROR_POINT
  2  (
  3    PTR_ID                   NUMBER                NOT NULL,
  4    PTR_FUNDSHARE            NUMBER(10)            NOT NULL,
  5    PTR_DATE                 DATE                  NOT NULL,
  6    PTR_INDEXID              NUMBER(10)            NOT NULL,
  7    PTR_FUNDSHARENAV         FLOAT(126)            NOT NULL,
  8    PTR_INDEXVALUE           FLOAT(126)            NOT NULL,
  9    PTR_BENCHMARKVALUE       FLOAT(126)            NOT NULL,
 10    PTR_INDEXDIV             FLOAT(126)            NOT NULL,
 11    PTR_SPOT                 FLOAT(126)            NOT NULL,
 12    PTR_CALCVALUE            FLOAT(126)            NOT NULL,
 13    PTR_USEDVALUE            FLOAT(126)            NOT NULL,
 14    PTR_TOBEUSEDFLAG         NUMBER(1)   DEFAULT 1 NOT NULL,
 15    PTR_USER                 VARCHAR2(200),
 16    PTR_COMMENT              VARCHAR2(250),
 17    CONSTRAINT PK_ETFTERRORPOINT PRIMARY KEY (PTR_ID),
 18    CONSTRAINT UK_ETFTERRORPOINT UNIQUE (PTR_FUNDSHARE, PTR_DATE) using index tablespace INDXLNS_M01,
 19    CONSTRAINT FK_ETFTERRORPOINT_OBJECT Foreign KEY (PTR_ID) References MUT_OBJECTS (OBJ_ID)
 20  );
 
CREATE TABLE ETF_TRACKING_ERROR_POINT
(
  PTR_ID                   NUMBER                NOT NULL,
  PTR_FUNDSHARE            NUMBER(10)            NOT NULL,
  PTR_DATE                 DATE                  NOT NULL,
  PTR_INDEXID              NUMBER(10)            NOT NULL,
  PTR_FUNDSHARENAV         FLOAT(126)            NOT NULL,
  PTR_INDEXVALUE           FLOAT(126)            NOT NULL,
  PTR_BENCHMARKVALUE       FLOAT(126)            NOT NULL,
  PTR_INDEXDIV             FLOAT(126)            NOT NULL,
  PTR_SPOT                 FLOAT(126)            NOT NULL,
  PTR_CALCVALUE            FLOAT(126)            NOT NULL,
  PTR_USEDVALUE            FLOAT(126)            NOT NULL,
  PTR_TOBEUSEDFLAG         NUMBER(1)   DEFAULT 1 NOT NULL,
  PTR_USER                 VARCHAR2(200),
  PTR_COMMENT              VARCHAR2(250),
  CONSTRAINT PK_ETFTERRORPOINT PRIMARY KEY (PTR_ID),
  CONSTRAINT UK_ETFTERRORPOINT UNIQUE (PTR_FUNDSHARE, PTR_DATE) using index tablespace INDXLNS_M01,
  CONSTRAINT FK_ETFTERRORPOINT_OBJECT Foreign KEY (PTR_ID) References MUT_OBJECTS (OBJ_ID)
)
 
ORA-00054: resource busy and acquire with NOWAIT specified

Le problème ici était forcément dû à une des 2 FK que le développeur souhaitait créer.
En testant séparément la création des 2 FK, j’ai pu conclure que le problème était lié à la table MUT_OBJECTS et que celle-ci devait surement être lockée par une autre session.

Voyons comment résoudre ce problème avec un exemple.

Test Case:

Session 1 :

SQL> create table t1 as select * from user_objects;

Table created.

SQL> alter table t1 add constraint pk_t1 primary key (object_id);

Table altered.

SQL> update t1 set STATUS='OK';

30755 rows updated.

La table T1 est donc lockée par la session 1.


Session 2:

SQL> create table t2 as select * from user_objects where rownum<=10;

Table created.

SQL> alter table t2 add constraint fk_t1 foreign key (object_id) references t1;
alter table t2 add constraint fk_t1 foreign key (object_id) references t1
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Je reproduis donc le même problème que mon développeur aujourd’hui.
Dans mon exemple je sais qu’on a un lock sur T1 dans la session 1 puisque c’est moi qui l’ait généré, mais comment peut-on trouver « Oraclement parlant » la session qui lock la table ?

La solution que j’ai utilisé consiste à requêter la vue v$LOCKED_OBJECT grâce à l’OBEJCT_ID que j’ai récupéré dans USER_OBJECTS. La vue v$LOCKED_OBJECT me donne la liste des locks de type DML en cours et notamment la session bloquante :

SQL> select object_id from user_objects where object_name='T1';

 OBJECT_ID
----------
     70929

SQL> select SESSION_ID from v$locked_object where object_id=70929;

SESSION_ID
----------
        17

Le coupable est donc la session 17.
On peut ensuite requêter la vue V$SESSION pour avoir plus d’informations sur celui ou celle qui lock la table :

SQL> select OSUSER,machine,PROGRAM from v$session where sid=17;

OSUSER                         MACHINE                                                          PROGRAM
------------------------------ ---------------------------------------------------------------- --------------------------------------
EUR\aaangour012711             EUR\FR9124662D                                                   sqlplus.exe

J’ai ainsi le nom du coupable (moi), le nom de la machine cliente et le programme utilisé.
Je peux ensuite prendre mon téléphone et appeler le LOCKEUR pour lui dire qu’il a intérêt à committer ou rollbacker sa transaction sur le champ sinon je lui kill sa session. Bien sûr je ne lui dis pas ça comme ça, mais ça traduit en tout cas ma pensée ;-).

Et effectivement en committant la transaction de la session 1 je peux créer la FK sur la table T2 :

Session 1 :

SQL> commit;

Commit complete.

Session 2 :

SQL> alter table t2 add constraint fk_t1 foreign key (object_id) references t1;

Table altered.

Voilà comment en utilisant les vues dynamiques de performance qu’Oracle met à notre disposition vous pouvez régler des problèmes Oracle de tous les jours.
N’hésitez pas à faire des DESCRIBE sur ces vues pour voir les infos disponibles.
La doc Oracle Database Reference est là pour vous aider à y voir plus claire.