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

For more information about Oracle, see Oracle topics page http://www.bkjia.com/topicnews.aspx? Tid = 12