How to diagnose blocking 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); the data is: id val1 12 21. In A Session, it is called Session, the following update statement is executed without submission or rollback. SQL> update t set val = 3 where id = 1; www.2cto.com 2. In another Session, it is called Session B and the following update statement is made, session B will be blocked. SQL> update t set val = 4 where id = 1; but 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 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 blockedsidfrom v $ lock blkingsess, rule blkingsess. id1 = rule. id1 and blkingsess. id2 = rule. id2and 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. www.2cto.com 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 alter system kill session '192, 7' based on the situation, where 142 is sid, 7 For serial # session 142, the following error will be received, and session139 will proceed to the subsequent steps. ERROR: ORA-03114: not connected to ORACLE author kkdelta