Blocking Sessions and Lock Kill Scripts!

December 23, 2019  

You can find historical blocking session and lock with below scripts. Change date time column which is specified with bold letters.

select v.sql_text,v.sql_fulltext,sub.* from v$sql v,

(select sample_time,s.sql_id sql_id, session_state, blocking_session,

owner||'.'||object_name||':'||nvl(subobject_name,'-') obj_name,s.program,s.module,s.machine

from dba_hist_active_sess_history s, dba_objects o

where sample_time between

to_date('22/12/2019 05:00:00','DD/MM/YYYY HH24:MI:SS')

and

to_date('28/12/2019 11:30:00','DD/MM/YYYY HH24:MI:SS')

and event = 'enq: TX - row lock contention'

and o.data_object_id = s.current_obj#

order by 1 desc) sub where sub.sql_id=v.sql_id;

You can find momentarily blocking session and lock with below scripts.

select s1.username || '@' || s1.machine

|| ' ( THIS SID=' || s1.sid || ' )  is blocking '

|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status

from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2

where s1.sid=l1.sid and s2.sid=l2.sid

and l1.BLOCK=1 and l2.request > 0

and l1.id1 = l2.id1

and l2.id2 = l2.id2 ;

 

You can generate Kill script of Blocking sessions like below.

SELECT 

'alter system kill session ''' || SID || ',' || s.serial# || ',@'||inst_id||''';',sid,username,serial#,process,NVL (sql_id, 0),

sql_address,blocking_session,wait_class,event,p1,p2,p3,seconds_in_wait

FROM gv$session s WHERE blocking_session_status = 'VALID'

OR sid IN (SELECT blocking_session

FROM gv$session WHERE blocking_session_status = 'VALID');