How to view session blocking of Oracle Database

We all know that Oracle will automatically handle deadlocks without human intervention. However, Oracle cannot handle session blocking on its own, resulting in resource waste.

We all know that Oracle will automatically handle deadlocks without human intervention. However, Oracle cannot handle session blocking on its own, resulting in resource waste.

We all know that Oracle will automatically handle deadlocks without human intervention. However, Oracle cannot handle session blocking by itself, which leads to resource waste and system performance consumption, at this time, we need to quickly find out the cause of the blocking and eliminate it as soon as possible so that the system can run normally again. The following is an example to explain how to quickly handle such blocking:

The following example shows how two sessions simultaneously update the same record of the HR user.

HR user window 1:

[Oracle @ linuxidc ~] $ Sqlplus hr/hr

SQL * Plus: Release 10.2.0.1.0-Production on Tue Dec 18 14:57:27 2012

Copyright (c) 1982,200 5, Oracle. All rights reserved.

Connected:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options

SQL> update hr. employees set last_name = 'B' where Fig = 100;

1 row updated.

HR user window 2:

[Oracle @ linuxidc ~] $ Sqlplus hr/hr

SQL * Plus: Release 10.2.0.1.0-Production on Tue Dec 18 14:56:49 2012

Copyright (c) 1982,200 5, Oracle. All rights reserved.

Connected:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options

SQL> update hr. employees set last_name = 'a 'where employee_id = 100;

Wait ...... Hang cannot move

Open a window and log in with the SYS User:

[Oracle @ linuxidc ~] $ Sqlplus/as sysdba

SQL * Plus: Release 10.2.0.1.0-Production on Tue Dec 18 14:58:31 2012

Copyright (c) 1982,200 5, Oracle. All rights reserved.

Connected:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options

--- Find blocking and blocked session id

SQL> select c. terminal | '(''' |. sid | ',' | c. serial # | ''') is blocking '| B. sid
| ',' | D. serial # block_msg, a. block from v $ lock a, v $ lock B, v $ session c, v $ session d
3 where. id1 = B. id1 and. id2 = B. id2 and. block> 0 and. sid <> B. sid and. sid = c. sid and B. sid = d. SID;

BLOCK_MSG
--------------------------------------------------------------------------------
BLOCK
----------
Pts/2 ('2014, 18 ') is blocking 151
1

SQL> select sid, serial #, username from v $ session where username is not null;

Sid serial # USERNAME
--------------------------------------------------
133 614 HR
134 71 SYS
138 298 SYSMAN
139 2 SYSMAN
140 2 SYSMAN
142 59 DBSNMP
147 175 DBSNMP
151 HR
152 1639 SYS
153 26 SYSMAN
159 13 SYS

11 rows selected.

--- Kill the blocker '2017, 8'

SQL> alter system kill session '2017, 18 ';

System altered.

Return to window 1 again:

SQL> select * from employees;
Select * from employees
*
ERROR at line 1:
ORA-00028: your session has been killed

Go back to HR user window 2 again and check that the statement has been executed (1 row updated prompt ):

SQL> update hr. employees set last_name = 'a 'where employee_id = 100;

1 row updated.