Killing session in Oracle RAC Database

Hi DBAs,
          Whenever we find some locking issue in RAC Database and Developers requested to kill the session which is blocking and creating lock.,please make sure you are killing the correct session.Below steps can be very much useful.

Step 1:Find the Blocking sessionSQL> SET LINES 1000

SQL> select process,sid,blocking_session,BLOCKING_INSTANCE from gv$session
where blocking_session is not null;  2

PROCESS                         SID BLOCKING_SESSION BLOCKING_INSTANCE
------------------------ ---------- ---------------- -----------------
24822                           1139             5366                 1


1 rows selected.


Step 2:Check the Program which is blocking

SELECT s.inst_id,
       s.sid,
       s.serial#,
       p.spid,
       s.username,
       s.program
FROM   gv$session s
       JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
where s.sid=1139;

So ,1139 is the CALC program

Step 3:Find the Session details

SQL> select sid,serial#, INST_ID from gv$session where sid=' 1139';

       SID    SERIAL#    INST_ID
---------- ---------- ----------
      1139      56959          3
     
Step 4:Kill the session immediately

SQL> alter system kill session '1139,56959,@3'  immediate;

System altered.



Hope it helps...

Enjoy Oracle RAC DBA learning...


Best regards,

Rafi.