Tuesday, 29 November 2016

Find Oracle blocking sessions

Oracle Blocking Session:

Blocking session occurs when one session acquired an exclusive lock on an object and doesn't release it, another session (one or more) want to modify the same data. First session will block the second until it completes its job.

Query For Blocking sessions:

select sid "Session id ", decode(Block,1, 'Blocking Session','Waiting Session') Details, ctime  "Blocking Time /Waiting time",id2 from v$lock where (block <> 0 or request <>0 ) and ctime > 200 order by id2,Details;

Query For Locking Session:

To get all the details of the locks held and session waiting for them and time of the lock, this script is very important to see if there any blocking sessions:

col username  for a10
col held for a12
col requested for a12

select sid, type "LOCK TYPE", id1, id2,
decode(lmode ,
1 , 'NULL',
2 , 'Row-S(SS)',
3 , 'Row-X(SX)',
4 , 'Share(S)',
5 , 'S/Row-X(SSX)',
6 , 'Exclusive(X)' ) HELD,
1 , 'NULL',
2 , 'Row-S(SS)',
3 , 'Row-X(SX)',
4 , 'Share(S)',
5 , 'S/Row-X(SSX)',
6 , 'Exclusive(X)' ) REQUESTED,
ctime "TIME(SEC)"
from v$lock
where id1 in
(select id1 from v$lock where request != 0 )
order by 2,3,5


Here for Normal case, you can kill the session by below query:

SQL>alter system kill session 'SID,SERIAL#';

But, the Question is how we will find the Blocking session in RAC environment.
To find out the blocking sessions in RAC, we can query gv$lock and gv$session views. But we can’t kill a session here in RAC as same as an ordinary blocking session.

Please find the below query to get the session details.

SQL> select inst_id,sid,serial# from gv$session where username='ORA-DATA';
   INST_ID        SID    SERIAL# 
---------- ---------- ---------- 
         4        119        519
SQL>  alter system kill session '119,519,4'; 
 alter system kill session '119,519,4' 
ERROR at line 1: 
ORA-00026: missing or invalid session ID

But now this will works:


SQL> alter system kill session '119, 519, @4’;
System altered.

OR another way also you can use following command to kill a session in Oracle RAC without waiting for confirmation to kill.

SQL>alter system kill session '119,519,@4' immediate;
System altered. 

Hope these below links may useful:

Hope the above information will be useful and helpful. Please let us know for any suggestions or comments either by comments box or write to us @contact us https://ora-data.blogspot.in/


1 comment:

  1. Hi guys,
    Here is a good explanation what is a blocking session and how to find it


Thank you for your comments and suggestions. It's good to talk you.