Search the Site

Sponsors

bottom corner

Identify database locks

This article notes how to quickly identify which user and which session is placing a lock on an object, and how to kill that session, if absolutely necessary.

This page is filed under keyword(s): oracle.

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.

Did you find this page useful? Please consider browsing other articles or subscribing to the RSS feed to keep up with latest.

This page is filed under keyword(s): oracle.
Author: C. Peter Chen
Last updated: 30 Jul 2008

bottom corner