1. Querying for locks in the database
SELECT * from V$lock;
SELECT * from V$lock where block=1;
2. Querying for Locked objects
SELECT * from V$locked_object;
3. Query blocking
Check for blocked sessions
SELECT * from V$lock where lmode=0 and type in (' TM ', ' TX ');
Check for blocking other session locks
SELECT * from V$lock where lmode>0 and type in (' TM ', ' TX ');
4. Querying the process that the database is waiting for a lock
SELECT * from v$session where lockwait are NOT null;
5. Query the relationship between session lock waits
Select A.sid holdsid,b.sid waitsid,a.type,a.id1,a.id2,a.ctime from V$lock A,v$lock b
where A.id1=b.id1 and A.id2=b.id2 and A.block=1 and b.block=0;
6. Query the lock wait event
SELECT * from v$session_wait where event= ' enqueue ';
Solution:
Select session_id from V$locked_object; --First get the session_id of the locked object
SELECT SID, Serial#, username, osuser from v$session where sid = session_id; --Get the SID and serial# of V$session through the session_id obtained above, then terminate the process.
ALTER SYSTEM KILL SESSION ' sid,serial ';
Example
ALTER SYSTEM KILL SESSION ' 13, 8 ';
Oracle Lock Table Processing