Con este ejemplo, puedes fácilmente identificar un bloqueo en tu base de datos Oracle via comandos.
Existen 3 vistas que pueden ayudarnos a identificar un bloqueo en oracle.
La manera más fácil de explicar esto es con un ejemplo.
Primero creemos una tabla donde vamos a realizar nuestras pruebas.
SQL> CREATE TABLE test (uno INTEGER, dos VARCHAR2(10));
Table created.
Ahora vamos a insertar algunos registros de prueba y ejecutamos commit.
INSERT INTO test VALUES(1,'AAAAAAAAAA');
INSERT INTO test VALUES(2,'BBBBBBBBBB');
INSERT INTO test VALUES(3,'CCCCCCCCCC');
INSERT INTO test VALUES(4,'DDDDDDDDDD');
INSERT INTO test VALUES(5,'EEEEEEEEEE');
SQL> commit;
Commit complete.
Identificamos nuestro SID con el cual estamos bloqueando la tabla.
SQL> select sys_context('USERENV','SID') SID from dual;
SID
-----------------------
142
Colocamos un bloqueo exclusivo en la tabla.
SQL> LOCK TABLE test IN EXCLUSIVE MODE;
Table(s) Locked.
Abrimos otra sesion en oracle, identificamos nuestros sid y procedemos a actualizar la tabla.
SQL> select sys_context('USERENV','SID') SID from dual;
SID
-----------------------
119
SQL> UPDATE test SET dos = 'XXXXXXXXXX' WHERE uno = 2;
Al terminar de ejecutar el update nuestra session se va a quedar esperando, esto es debido a que ya tenemos bloqueada la tabla por medio de un bloqueo exclusivo. Supongamos que nos llama un usuario y nos dice que su session no responde. En este caso primero debemos identificar si existe un bloqueo en la base de datos, para eso podemos hacer un select sobre la vista DBA_LOCK, y si encontramos una session con un valor de Blocking en la columna BLOCKING_OTHERS entonces existe un bloqueo a otro usuario.
SQL> SELECT SESSION_ID,LOCK_TYPE, MODE_HELD FROM dba_locks
WHERE BLOCKING_OTHERS = 'Blocking';
SESSION_ID LOCK_TYPE MODE_HELD
---------- -------------------------- ----------------------------------------
142 DML Exclusive
Para ver solo el ID del bloqueo.
SQL> SELECT * FROM DBA_BLOCKERS;
HOLDING_SESSION
---------------
142
Para ver a quien esta siendo bloqueado
SQL> SELECT * FROM DBA_WAITERS;
WAITING_SESSION HOLDING_SESSION LOCK_TYPE MODE_HELD MODE_REQUESTED
--------------- --------------- ---------- ---------- ---------------
119 142 DML Exclusive Row-X (SX) 81714
En este podemos ver que existen una sesion bloqueadas por el usuario 142.
Para matar esa session tenemos que identificar su SERIAL#
SQL> SELECT SID,SERIAL# FROM V$SESSION WHERE SID = 142
SID SERIAL#
---------- ----------
142 63166
Ahora si podemos eliminar esa session (No es conveniente estar matando todas las sessiones que esten bloqueando usuario, debemos siempre de identificar el problema que lo causa, alguna transaccion abierta dejada por algún desarrollador, un error en la aplicación, etc)
SQL> ALTER SYSTEM KILL SESSION '142,63166';
System altered.
Si revisamos nuevamente, ya no vamos a encontrar registros que esten bloqueando.
SQL> SELECT * FROM DBA_WAITERS
no rows selected
Quisiera anexar algunos queries más que también nos muestran los bloqueos:
select l1.sid, ' IS BLOCKING ', l2.sid from v$lock l1, v$lock l2 where l1.block =1 and l2.request > 0 and l1.id1=l2.id1 and l1.id2=l2.id2;
Para RAC podemos usar el siguiente
SELECT substr(DECODE(request,0,'Holder: ','Waiter: ')||sid,1,12) 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;
Espero les sea de utilidad.