Query sessions holding LOCK

Published by Alessandro Caredda on 7 March 20237 March 2023

With the article “Query sessions holding LOCK,” we can identify the session that is blocking access to other sessions on the contested object.

We have already discussed in previous articles about disputes in the Oracle database: Query LOCK and number of Oracle sessions and…

The main cause of disputes within the Oracle database concerns the numerous sessions accessing the same tables. These locking situations, in some cases, could result in significant bottlenecks translated into database blocks.

Our expertise lies in the agility and promptness to identify which session is causing the drama for many users and, therefore, our responsible managers.

The goal is to identify and reassure everyone by indicating that you have found the cause of the problem.

First of all, we head to scriptDBA.com, go to the Oracle session management section, and search for the queries related to LOCK to quickly identify the anomaly.

With the following query, we can identify the SID of the session that is blocking the session with SID.

Query sessions holding LOCK:

select 'SID ' || l1.sid ||' is blocking ' || l2.sid blocking 
from v$lock l1, v$lock l2 
where l1.block =1 and 
      l2.request > 0 and 
      l1.id1=l2.id1 and 
      l1.id2=l2.id2;

Once identified, we need to provide as many details as possible about the SIDs (Blocking and Blocked) to the relevant application group, so that we can be authorized to execute a possible DISCONNECT or KILL which can be performed with the statement available in the article “Query Oracle session disconnect”.

To view my guides on CRS commands click here!

At this LINK, however, you will find SQL queries

Stay updated on our activities by joining our community:
Facebook
Youtube
Linkedin

For any doubt, do not hesitate to comment on the article.