We value your privacy

We use cookies to enhance your browsing experience, serve personalised content, and analyse our traffic. By clicking "Accept All", you consent to our use of cookies.

Customise Consent Preferences

We use cookies to help you navigate efficiently and perform certain functions. You will find detailed information about all cookies under each consent category below.

The cookies that are categorised as "Necessary" are stored on your browser as they are essential for enabling the basic functionalities of the site. ... 

Always Active

Necessary cookies are required to enable the basic features of this site, such as providing secure log-in or adjusting your consent preferences. These cookies do not store any personally identifiable data.

No cookies to display.

Functional cookies help perform certain functionalities like sharing the content of the website on social media platforms, collecting feedback, and other third-party features.

No cookies to display.

Analytical cookies are used to understand how visitors interact with the website. These cookies help provide information on metrics such as the number of visitors, bounce rate, traffic source, etc.

No cookies to display.

Performance cookies are used to understand and analyse the key performance indexes of the website which helps in delivering a better user experience for the visitors.

No cookies to display.

Advertisement cookies are used to provide visitors with customised advertisements based on the pages you visited previously and to analyse the effectiveness of the ad campaigns.

No cookies to display.

  Master in PostgreSQL DBA |Join Free Demo Session on 12 May 2025 at 7.30 PM IST| Register for Free Demo

Know More

Blocking in Oracle Database

Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
  • User AvatarKiran Dalvi
  • 10 Jul, 2021
  • 0 Comments
  • 3 Mins Read

Blocking in Oracle Database

Blocking in Oracle Database

Vintage Logotype Etsy Banner
Blocking Sessions How to find Blocking Sessions Blocking sessions occur when one sessions holds an exclusive lock on an object and doesn’t release it before another sessions wants to update the same data. This will block the second session until the first session has done its work. 1. Simulation 2. Finding Out Who’s Holding a Blocking Lock 3. Solution
1. Simulation Session 1:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
[oracle@upgrade ~]$ sqlplus user1/user1;
 
SQL> create table test (name varchar2(1));
 
Table created.
 
SQL> insert into test values ('ankush');
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> select * from test where name='ankush' for update;
 
name
-
ankush
 
SQL>
SQL> select sys_context('USERENV','SID') from dual;
 
SYS_CONTEXT('USERENV','SID')
----------------------------------------------------
50
 
SQL>
Session 2:
 
In second session try to update the rows which you have selected above.
 
[oracle@upgrade ~]$ sqlplus user1/user1;
 
SQL> select sys_context('USERENV','SID') from dual;
 
SYS_CONTEXT('USERENV','SID')
---------------------------------------------------
60
 
SQL>
SQL> update test set name='x' where name='ankush';
 
-- hanging here --
..
..
2. Finding Out Who’s Holding name Blocking Lock
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
sqlplus / as sysdba
 
SELECT s1.username || '@' || s1.machine
    || ' ( SID=' || s1.sid || ' )  is blocking '
    || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
    FROM gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
    WHERE s1.sid=l1.sid AND s2.sid=l2.sid
    AND l1.BLOCK=1 AND l2.request > 0
    AND l1.id1 = l2.id1
    AND l1.id2 = l2.id2;
     
select sid, serial#, username,status from v$session where sid in ('holder','waiter');
 
-- OR --
 
select decode(request,0,'Holder: ','Waiter: ')||sid sess,
id1, id2, lmode, request, type
from gv$lock
where (id1, id2, type) in
(select id1, id2, type from gv$lock where request>0)
order by id1, request;
 
-- OR --
 
SELECT
   s.blocking_session,
   s.sid,
   s.serial#,
   s.seconds_in_wait
FROM
   gv$session s
WHERE
   blocking_session IS NOT NULL;
 
-- OR --
    
SELECT
   l1.sid || ' is blocking ' || l2.sid blocking_sessions
FROM
   gv$lock l1, gv$lock l2
WHERE
   l1.block = 1 AND
   l2.request > 0 AND
   l1.id1 = l2.id1 AND
   l1.id2 = l2.id2;
    
-- OR --
 
SELECT sid, id1 FROM v$lock WHERE TYPE='TM';
SELECT object_name FROM dba_objects WHERE object_id='&object_id_from_above_output';
select sid,type,lmode,request,ctime,block from v$lock;
select blocking_session, sid, wait_class,
seconds_in_wait
from v$session
where blocking_session is not NULL
order by blocking_session;
Output:
 
SQL> SELECT s1.username || '@' || s1.machine
  2      || ' ( SID=' || s1.sid || ' )  is blocking '
    || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
    FROM v$lock l1, v$session s1, v$lock l2, v$session s2
  3    4    5      WHERE s1.sid=l1.sid AND s2.sid=l2.sid
  6      AND l1.BLOCK=1 AND l2.request > 0
  7      AND l1.id1 = l2.id1
  8      AND l1.id2 = l2.id2;
 
BLOCKING_STATUS
----------------------------------------------------------------------------------
user1@upgrade.oracle.com ( SID=50 )  is blocking user1@upgrade.oracle.com ( SID=60 )
 
SQL> select sid, serial#, username,status from v$session where sid in (50,60);
 
       SID    SERIAL# USERNAME             STATUS
---------- ---------- -------------------- --------
        60         77 user1                   ACTIVE <-- waiter
        50         13 user1                   INACTIVE <-- Holder
 
SQL>
 
 
SQL>
 
-- OR --
 
SQL> select decode(request,0,'Holder: ','Waiter: ')||sid sess,
id1, id2, lmode, request, type
from gv$lock
where (id1, id2, type) in
(select id1, id2, type from gv$lock where request>0)
order by id1, request;  2    3    4    5    6
 
SESS                                                    ID1        ID2      LMODE    REQUEST TY
------------------------------------------------ ---------- ---------- ---------- ---------- --
Holder: 50                                           589826       1571          6          0 TX
Waiter: 60                                           589826       1571          0          6 TX
 
SQL>
 
-- OR --
 
SQL> SELECT
   s.blocking_session,
   s.sid,
   s.serial#,
   s.seconds_in_wait
FROM
   gv$session s
WHERE
   blocking_session IS NOT NULL;  2    3    4    5    6    7    8    9
 
BLOCKING_SESSION        SID    SERIAL# SECONDS_IN_WAIT
---------------- ---------- ---------- ---------------
              50         60         77            1626
 
SQL>
 
-- OR --
 
SQL> SELECT
   l1.sid || ' is blocking ' || l2.sid blocking_sessions
FROM
   gv$lock l1, gv$lock l2
WHERE
   l1.block = 1 AND
   l2.request > 0 AND
   l1.id1 = l2.id1 AND
   l1.id2 = l2.id2;  2    3    4    5    6    7    8    9
 
BLOCKING_SESSIONS
----------------------------------------------------------
50 is blocking 60
 
SQL>
3. Solution Inform to the holder to commit/rollback. — OR —- kill the holder session if you have appropriate permision
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
alter system kill session 'SID,SERIAL#';(For Single instance)
 
SQL> alter system kill session '50,13';
 
System altered.
 
SQL>
 
-- After killing holder session, waiter session got completed
 
SQL> update test set name='x' where name='ankush';
 
1 row updated.
 
SQL>
Change Hostname in Oracle Linux 7
User password Backup