Identifier les verrous d'objets et les sessions bloquantes sous Oracle
Requêtes de diagnostic pour trouver la session qui détient un verrou, les objets verrouillés et les sessions bloquées sous Oracle.
Lorsqu'une session attend un verrou, il faut identifier la session qui le détient, les objets concernés et les sessions bloquées. Cet article regroupe les requêtes de diagnostic sous Oracle.
Sessions bloquées et bloquantes
La vue DBA_WAITERS liste directement les sessions en attente et la session qui détient le verrou :
SELECT waiting_session, holding_session, lock_type, mode_held
FROM dba_waiters;
Les sessions dont l'attribut BLOCKING_SESSION est renseigné attendent une autre session :
SELECT sid, serial#, username, status, blocking_session
FROM v$session
WHERE blocking_session IS NOT NULL;
Objets verrouillés
SELECT d.OBJECT_ID, d.OBJECT_NAME, l.SESSION_ID, l.ORACLE_USERNAME, l.LOCKED_MODE
FROM v$locked_object l, dba_objects d
WHERE d.OBJECT_ID = l.OBJECT_ID;
Pour relier une session à son processus système (utile pour identifier le processus côté système d'exploitation) :
SELECT s.sid, s.serial#, s.username, p.spid AS os_process_id
FROM v$session s
JOIN v$process p ON s.paddr = p.addr
WHERE s.sid = <SID>;
Transactions et verrous bruts
SELECT ADDR, KADDR, SID, TYPE, ID1, ID2, LMODE, BLOCK
FROM v$lock;
SELECT a.sid, a.saddr, b.ses_addr, a.username, b.xidusn, b.used_urec, b.used_ublk
FROM v$session a, v$transaction b
WHERE a.saddr = b.ses_addr;
Tuer la session bloquante
Une fois la session bloquante identifiée, voir l'article sur l'erreur ORA-00054 pour la procédure d'arrêt (ALTER SYSTEM KILL SESSION).
Remarques
- Une vue arborescente complète des verrous (script
lock_tree.sql) est disponible sur oracle-base.com : https://oracle-base.com/dba/10g/lock_tree.sql - Les
ALTER TABLE ... DISABLE TABLE LOCKempêchent les DML sur la table. Ne les utiliser qu'en connaissance de cause.