List the resources blocked in Oracle

Let’s find out who is locking a particular table in Oracle, and which machine is locking it from

When you perform an operation on a resource, such as an insert in the tables, this resource (in this case the table) enters a state of lock which serves to prevent others from modifying it, until our operation is finished and let’s do a final commit.

If a table is locked, it means that those who are not modifying it cannot even execute a read query on it (whether it is a user or an automatic process), which will be running until the table is locked. disabled, when the operation is finished. If, therefore, by executing a query on a table, we see that the query is not responding, most likely it means that someone is typing in it, and to find out who it is we can execute this query.

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;

The query tells us exactly which table (and in which owner / schema it is located) it is occupied, by which user it is occupied, and in which machine the user is logged in to perform the operation that is blocking it.

We periodically check the functioning of the links in our articles. If you notice any links that do not work, please let us know in the comments. If you enjoyed the article consider supporting the blog with a small donation. Thank you. Patreon / Ko-fi / Liberapay / Paypal

Leave a Reply

Your email address will not be published. Required fields are marked *