Già in un precedente articolo ho affrontato come vedere i lock Oracle con una query. Con questo articolo vorrei approfondire l’argomento proponendo una query più evoluta per visualizzare i lock.

La limitazione di quanto presentato nel precedente articolo era dovuta alla difficoltà di analizzare la risorsa loccata.

Per poter fare un’analisi più dettagliata entrano in gioco più tabelle e viste di sistema:

• v$session: in questa tabella vengono memortizzate tutte le sessioni attive
• v$lock: contiene i riferimenti ai lock per sessione
• dba_objects: contiene tutti i tipi di oggetti disponibili in Oracle
• v$locked_object: memorizza tutti i lock presenti sui vari oggetti

Mettendo in join queste tabelle è possibile ricavare una discreta profilazione del blocco. In particolare con la seguente query è possibile individuare:

• kill id: l’identificativo della sessione bloccante e che quindi potremmo decidere di eliminare per rilasciare i lock
• blocking status: individua l’utente e la macchina bloccanti e quelli bloccati
• object name: il nome dell’oggetto loccato
• object type: la tipologia dell’oggetto bloccato

Ecco il SQL necessario:

SELECT (s1.sid || ',' || s1.serial#) AS kill_id,
       s1.username || '@' || s1.machine ||
       ' ( SID=' || s1.sid || ' )  is blocking ' ||
       s2.username || '@' || s2.machine ||
       ' ( SID=' || s2.sid || ' ) ' AS blocking_status,
       O.object_name,
       O.object_type
FROM v$lock l1,
     v$session s1,
     v$lock l2,
     v$session s2,
     dba_objects O,
     v$locked_object L
WHERE s1.sid = l1.sid
AND   s2.sid = l2.sid
AND   l1.block = 1
AND   l2.request > 0
AND   l1.id1 = l2.id1
AND   l2.id2 = l2.id2
AND   s1.sid = L.session_id;
AND   l1.object_id = O.object_id;

A questo punto per cancellare una sessione loccante sarà sufficiente sostituire a $KILL_ID nella prossima query il valore ottenuto dall’esecuzione del SQL precedente.

ALTER SYSTEM KILL SESSION '$KILL_ID';