Query to Identify Sessions Blocked by Another Session

Published by Patrizia Gardis on

Query to Identify Sessions Blocked by Another Session


Well, before diving into the article “Query to Identify Sessions Blocked by Another Session,” we’d like to introduce this website to you if you’re seeing it for the first time!

Scriptdba is the first vertical website on Oracle databases completely in Italian. Actually, during 2020, we’ll be launching the English version as well, but Italian will always be there, regardless.

Did you get it right? A bilingual website. Why? Because we like to keep things simple, simple for you readers, of course! (A bit less so for us).

Query per individuare le sessioni bloccate da un'altra sessione
Hard disk and database icon isolated on white background

At scriptdba.com, you will find guides, tutorials, and troubleshooting for the most enterprise-level database out there!

With the Blocking LOCK query, we can identify the session that is blocking other sessions. These locking situations could, in some cases, lead to significant bottlenecks and therefore severe slowdowns, if not database locking. With the following query, you will determine which session is causing the slowdowns or database lock in a short time


Query to locate the session that is blocking other sessions:

select a.inst_id as WInst ,a.sid as WSid, a.serial# as WSer, a.sql_id as WSql , a.seconds_in_wait as WSec,
 b.sid as BSid , b.serial# as BSer , a.event as WEvent , b.event as BEvent, b.sql_id as BSql
from gv$session a , gv$session b
where a.BLOCKING_SESSION_STATUS='VALID' and b.inst_id=a.BLOCKING_INSTANCE and b.sid=a.BLOCKING_SESSION;

Stay tuned! Not just Query to see blocked sessions, in fact…

To view our guides on CRS Commands click here! At this LINK, however, you will find the SQL queries



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

For those who haven’t done so yet, we recommend saving this site in your bookmarks, so as to always have our solutions to the most common database problems at hand, especially Oracle databases.


0 Comments

Leave a Reply

Avatar placeholder