Diagnose blocked sessions in Oracle

Due to the lock mechanism, when execution of a DML or ddl SQL statement is blocked, you need to find out why the SQL statement is blocked. The following describes the common diagnostic methods:

Suppose there is a table: Table T (ID int primary key, Val INT); Data is:

ID	Val
1	1
2	2

1. In a session, session A is called here, and the following update statement is executed, not submitted or rolled back.
SQL> Update t set val = 3 where id = 1;

2. In another session, session B is called session B and the following update statement is executed. Session B will be blocked.

SQL> Update t set val = 4 where id = 1;

However, when an active transaction locks an object, there will be records such as object_id and session_id in the V $ locked_object view. The xidusn, xidslot, and xidsqn fields of blocked sessions are usually empty. the session whose session_id is 139 is blocked.

Select DBO. * from V $ locked_object Lo, dba_objects DBO where Lo. object_id = DBO. object_id and Lo. xidusn = 0

By querying v $ lock, you can see which session is blocked and which session: 142 is blocked by 139.

With blkedsess as (select * from V $ lock where request! = 0)
Select blkingsess. Sid blockingsid, blkedsess. Sid blockedsid
From v $ lock blkingsess, blkedsess
Where blkingsess. id1 = blkedsess. id1
And blkingsess. Id2 = blkedsess. Id2
And blkingsess. Sid! = Blkedsess. Sid

You can view session-related information through V $ session. The blocked status is generally active. You can also use SQL _address and V $ SQL to find the blocked SQL statement.

Select Sid, serial #, status, SQL _address from V $ session where Sid in (139,142)

Select * from V $ SQL where address = '6be7d33c'

At this time, the DBA can contact the blocked session to end the transaction or use the command to terminate the session as needed.

Alter system kill session '2017, 7'; where 142 is Sid and 7 is serial #

Session 142 will receive the following error, and session139 will proceed to the subsequent steps.

Error:
ORA-03114: not connected to Oracle