How to find/kill the locked objects in an oracle database

The below query can be used for determining the if any object locked in an oracle database.

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

And if you need to kill any session you can use the below query to do the same.

alter system kill session sid,serial;

In Category: Technical

Ravi Shankar

A Software developer and blogger who is always looking to provide technical help to the wider community.

Show 3 Comments
  • nilesh mehta July 12, 2010, 1:07 pm

    select c.owner, c.object_name, c.object_type, b.sid, b.serial#,
    b.status, b.osuser, b.machine
    from
    v$locked_object a , v$session b, dba_objects c

    these queries must be optimized as they may give performance issues.

  • Devanathan November 10, 2010, 12:12 pm

    Hi,

    Thanks

Leave a Comment

Get your free copies of the following tech guides by joining the Digital Answers mailing list.