How to Check Blocking Sessions in Oracle Database

 Introduction

In Oracle databases, a blocking session happens when one session holds a lock on a resource, preventing other sessions from continuing. As a DBA, finding and resolving blocking sessions is critical to keep the system responsive.

Step 1: Identify Blocking Sessions

Run the following query:

SELECT s.sid, s.serial#, s.username, s.osuser, s.machine,

       l.type, l.id1, l.id2, l.lmode, l.request

FROM v$session s

JOIN v$lock l ON s.sid = l.sid

WHERE l.block = 1;

This shows sessions that are blocking others.

Step 2: Find Sessions Being Blocked

Use this query:

SELECT s.sid, s.serial#, s.username, s.osuser, s.machine,

       l.type, l.id1, l.id2, l.request

FROM v$session s

JOIN v$lock l ON s.sid = l.sid

WHERE l.request > 0;

These are the blocked sessions waiting for a lock.

Step 3: View Both Blocking and Blocked Together

SELECT blocking_session, sid AS blocked_session, serial#

FROM v$session

WHERE blocking_session IS NOT NULL;

This quickly shows who is blocking whom.

Step 4: Kill the Blocking Session (If Needed)

If a blocking session is critical, avoid killing it.

But if necessary:

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

Conclusion

Blocking sessions are common in busy systems. Regular monitoring and proper SQL tuning help reduce blocking issues. Use these queries to identify and act quickly when problems arise.