Identify database locks

This select statement can be run by someone with DBA privileges (as it makes use of v$ views) to list who is locking what.

select a.session_id,
  c.serial#,
  a.oracle_username,
  a.os_user_name,
  c.machine,
  a.locked_mode,
  b.object_name,
  b.object_type
from v$locked_object a,dba_objects b, v$session c
where a.object_id = b.object_id
  and a.session_id = c.sid;

The query below is more specific. With it, you will be able to identify exactly which session is block another session, if any such conditions exist.

select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' as blocking
from v$lock l1, v$session s1, v$lock l2, v$session s2
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;

If necessary, we can use the following example to kill a particular session. It may be useful if the locking session is actually from a hanging process, for example, though typically we should have the user release his/her lock gracefully.

alter system kill session '123,5555' immediate;

Note that the “123” above should be replaced by the session ID, and the “5555” should be replaced by the serial #. Those two values are shown in the first two fields in the select SQL statement above.

Leave a Reply Cancel reply