CORE DBA SCRIPTS: 12/03/24

1. Simulation

Session 1:

[oracle@rac2 ~]$ sqlplus sh/sh;

SQL> create table t (a varchar2(1));

Table created.

SQL> insert into t values ('z');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t where a='z' for update;

A
-
z

SQL>
SQL> select sys_context('USERENV','SID') from dual;

SYS_CONTEXT('USERENV','SID')
----------------------------------------------------
46

SQL>

Session 2:

In second session try to update the rows which you have selected above. 

[oracle@rac2 ~]$ sqlplus sh/sh;

SQL> select sys_context('USERENV','SID') from dual;

SYS_CONTEXT('USERENV','SID')
---------------------------------------------------
39

SQL>
SQL> update t set a='x' where a='z';

-- hanging here --
..
..

2. Finding Out Who’s Holding a Blocking Lock

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
----------------------------------------------------------------------------------
SH@rac2.rajasekhar.com ( SID=46 )  is blocking SH@rac2.rajasekhar.com ( SID=39 )

SQL> select sid, serial#, username,status from v$session where sid in (46,39);

       SID    SERIAL# USERNAME             STATUS
---------- ---------- -------------------- --------
        39         77 SH                   ACTIVE <-- waiter
        46         13 SH                   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: 46                                           589826       1571          6          0 TX
Waiter: 39                                           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
---------------- ---------- ---------- ---------------
              46         39         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
----------------------------------------------------------
46 is blocking 39

SQL>

3. Solution


Inform to the holder to commit/rollback.

--- OR ----

kill the holder session, if it is ok

Syntax: alter system kill session 'SID,SERIAL#,@INST_ID'; (For RAC)
alter system kill session 'SID,SERIAL#';(For Single instance)

SQL> alter system kill session '46,13';

System altered.

SQL>

-- After killing holder session, waiter session got completed

SQL> update t set a='x' where a='z';

1 row updated.

SQL>

How to find Blocking Sessions