Finding locked objects in Oracle


If you’re wondering why particular query or procedure is talking a long such a long time to run; then make sure you check out whether the object you’re accessing is locked or not. There are quite a few dba views available to make our task less complicated. You can use the below query which will return you the list of locked objects.

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;

You can now decide whether to kill the job/ process or someother roundabout to remove the lock obtained on the particular object.

3 Comments

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.