Individuare Tabelle in Lock

Per individuare la sessione in attesa 

select * 

from V$SESSION_WAIT

 where EVENT = 'enqueue';

SID identifier of session holding the lock

P1, P2, P3 determine the resource when event = 'enqueue'

SECONDS_IN_WAIT gives how long the wait did occurs

SELECT substr(DECODE(request,0,'Holder: ','Waiter: ')||sid,1,17) sess,id1, id2, lmode, request, type, inst_id FROM GV$LOCK

WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM GV$LOCK WHERE request>0) ORDER BY id1, request

Per individuare la sessione bloccante e quindi che trattiene le risorse

select * from v$lock  where block=1;

select sid, serial#, username
from v$session where sid in
(select blocking_session from V$Session );

sid, serial# sono gli identificativi della sessione oracle

LOCKWAIT address of the lock waiting, otherwise null

ROW_WAIT_OBJ# object identified of the object we are waiting on (object_id of dba_objects)

 ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# sono i file_id , block_id and  row location within block of the locked row

select * from dba_blockers;
holding_session = 82

select holding_session as sessione_bloccante, waiting_session as sessione_bloccata , a.*
from dba_waiters a;

select *
from v$lock
where id1 = 1966084 and id2 = 735956 ;
---where sid = 882;

Per individua tabelle in lock e prendere il valore del campo session_id da killare. 

SELECT d.OBJECT_ID, substr(OBJECT_NAME,1,20), l.SESSION_ID, l.ORACLE_USERNAME, l.LOCKED_MODE 
from v$locked_object l, dba_objects d 

where d.OBJECT_ID=l.OBJECT_ID;

SOLUZIONE: Killare la sessione bloccante. 

SQL > ALTER SESSION KILL SESSION 'sid, serial#'