Session Blocker detection and managing locks

First we will find out who is blocking which user for how long.

select 
   blocking_session, 
   sid, 
   serial#, 
   module,
   wait_class,
   status,
   seconds_in_wait
from 
   gv$session
where 
   blocking_session is not NULL
order by 
   seconds_in_wait desc

 
















As above screen shot shows session with (SID) 12939 is blocking Session (SID=7063 SERIAL#=19791.

Now we will use victim sessions sid and serial# to find out exact SID and serial# of Blocker(Culprit) session.
 

select BLOCKER_SID,BLOCKER_SESS_SERIAL# from gv$session_blockers where sid=7063 and sess_serial#=19791


BLOCKER_SID BLOCKER_SESS_SERIAL#
----------- --------------------
      12939                     61551
1 row selected.

So main culprit session which is SID=19939 AND SERIAL#=61551. Now we will confirm that no other session is blocking it. if no row is returned then this is confirmation that this is first session which is blocking all other sessions.

select * from gv$session where sid=12939 and serial#=61551 and blocking_session is not null;

no row selected.

Now we are sure that this session is main culprit. Lets check what kind of activity it is doing using SQL_ID to view sql text of sql executed by this session.


select username,module,SQL_ID, STATUS,INST_ID from gv$session where sid=12939 and serial#=61551 


USERNAME, MODULE SQL_ID                  STATUS       INST_ID
MASTERSCHEMA DBMS_SCHEDULER 9cqw7mgvt39pt ACTIVE        2


----------------------------------
 select sql_FULLTEXT from v$sql where sql_id='9cqw7mgvt39pt'


Now if you want to wait for this session to finish its job, you can wait or you can kill this session by connecting to corresponding instance.

ALTER SYSTEM KILL SESSION '12939,61551';