How to view the list of locks on Oracle v9-11 database

in #oracle6 years ago

Coding-aeon.png

This query will let you easily see the list of locks on database objects for Oracle v9 to 11.

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;

It has proven really useful when encountering this particular issue that blocks you from performing updates on specific objects in your schema.
Most often this happens on tables and it is caused by other processes performing DML instructions or other routines on specific objects.
It is actually a feature that Oracle uses to protect data integrity, not allowing more than one process to modify exactly the same data simultaneously.
The problem is that sometimes they get stuck because of poorly developed procedures or instructions getting terminated and other issues.

Thank you! Stay tuned for more coding tips, tricks and much more!

resh aquasummit.png