STEPS TO FIND A BLOCKING SESSIONS



Written By :- Harish Ramakkagari

Tested By  :- Harish Ramakkagari 

Contact :-  harish.ramakkagari@attra.com.au   +919591792886  or harishr227@gmail.com  +918880598503




Steps:-


   1) select instance_name||' - '||status||' - '||startup_time||' - '||host_name||' - '||sysdate from gv$instance;

   2) select sid from gv$lock where block=1;

   3) select sql_text from gv$sqltext where hash_value=(select prev_hash_value from gv$session where sid=&sid);
   
   4) select sid||' - '||serial#||' - '||osuser||' - '||username||' - '||machine||' - '||status||' - '||logon_time
      from gv$session where sid=&sid;

   5)set pagesize 100
     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;
      
      (Hear we identify Holders & waiters)


   6) alter system kill session '&sid,&serial' immediate;