Query to find blocking sessions in oracle database and how to kill blocking sessions.

 

This document describes how to find the blocking sessions in the database and how to kill them. 

We can find blocking sessions by using v$session (standalone database or instance vise in RAC database) view or gv$session (RAC database) view.

Query to find blocking sessions:

--------------------------------------

select 

   blocking_session, 
   sid, 
   serial#, 
   wait_class,
   seconds_in_wait
from 
   gv$session
where 
   blocking_session is not NULL
order by blocking_session;

 

How to kill blocking session:

-------------------------------------

Find the blocking session by using above query and kill it using below query.

RAC database:

------------------

alter system kill session('SID,SERIAL#,@inst_id') immediate;

Example: alter system kill session ('65,32555,@1') immediate;

Non-RAC database:

------------------------

alter system kill session('SID,SERIAL#') immediate;

Example: alter system kill session('SID,SERIAL#,@1) immediate;

Thanks for going through the document.......................