ORA-00054 : ressource occupée et NOWAIT demandé ou délai expiré
Identifier les objets verrouillés, tuer la session bloquante et configurer DDL_LOCK_TIMEOUT pour l'erreur ORA-00054.
L'erreur ORA-00054 apparaît lorsqu'une instruction DDL, ou un SELECT ... FOR UPDATE NOWAIT, ne peut pas obtenir le verrou d'une ressource, ou lorsque le délai d'attente du verrou expire.
Identifier les objets verrouillés
La requête suivante associe chaque objet verrouillé à la session, au processus système et à la dernière instruction SQL :
SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM, S.USERNAME,
S.MACHINE, S.PORT, S.LOGON_TIME, SQ.SQL_FULLTEXT
FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S,
V$PROCESS P, V$SQL SQ
WHERE L.OBJECT_ID = O.OBJECT_ID
AND L.SESSION_ID = S.SID
AND S.PADDR = P.ADDR
AND S.SQL_ADDRESS = SQ.ADDRESS;
Une version plus courte donne le mode de verrou détenu :
SELECT lo.session_id, lo.oracle_username, lo.os_user_name,
lo.process, do.object_name,
DECODE(lo.locked_mode, 0, 'None', 1, 'Null', 2, 'Row Share (SS)',
3, 'Row Excl (SX)', 4, 'Share', 5, 'Share Row Excl (SSX)',
6, 'Exclusive', TO_CHAR(lo.locked_mode)) mode_held
FROM v$locked_object lo, dba_objects do
WHERE lo.object_id = do.object_id
ORDER BY 1, 5;
Tuer la session bloquante
Repérer le SID et le SERIAL# dans V$SESSION, puis tuer la session :
SQL> SELECT sid, serial#, username, status FROM v$session WHERE sid = 271;
SID SERIAL# USERNAME STATUS
---------- ---------- ------------------------------ --------
271 1409 APP_USER INACTIVE
SQL> ALTER SYSTEM KILL SESSION '271,1409' IMMEDIATE;
System altered.
Attendre le verrou au lieu d'échouer
Le paramètre DDL_LOCK_TIMEOUT indique, en secondes, combien de temps une instruction DDL attend un verrou avant de générer l'erreur :
ALTER SYSTEM SET ddl_lock_timeout = 20;
Remarques
- Tuer une session annule sa transaction non validée. Vérifier auprès de l'équipe applicative avant de le faire.
- Une session dans l'état
INACTIVEpeut tout de même détenir des verrous si elle a une transaction ouverte.