Blocking session, find root blockers and kill

 

By the following script you are able to find root cause of the locks. Script do not display BACKGROUND processes that are locking others (for example, LGWR, DBWR), because killing them causes database crush.

In front of the kill immediate statement there is written additional information such as : username[machine]:sql_id[prev_sql_id]:program and then comes kill immediate statement.

with lk as (select blocking_instance||'.'||blocking_session blocker, inst_id||'.'||sid waiter
 from gv$session where blocking_instance is not null and blocking_session is not null and username is not null)
 select lpad(' ',2*(level-1))||waiter lock_tree from
 (select * from lk
 union all
 select distinct 'root', blocker from lk
 where blocker not in (select waiter from lk))
 connect by prior waiter=blocker start with blocker='root';
--Generate SQLs to kill top-level blockers
set serverout on
 declare
 sess varchar2(20);
 sessinfo varchar2(100);
 begin
 for i in
 (with lk as (select blocking_instance||'.'||blocking_session blocker, inst_id||'.'||sid waiter
 from gv$session where blocking_instance is not null and blocking_session is not null and username is not null)
 select distinct blocker from lk where blocker not in (select waiter from lk)
 )
 loop
 begin
 select regexp_substr(i.blocker,'[0-9]+$')||','||serial# ||',@' || regexp_substr(i.blocker,'[0-9]+'),
 substr(username||'['||machine||']:'||sql_id||'['||prev_sql_id||']:'||program,1,100) into sess, sessinfo
 from gv$session where inst_id = regexp_substr(i.blocker,'[0-9]+') and sid = regexp_substr(i.blocker,'[0-9]+$') and type='USER';
 dbms_output.put_line(sessinfo || ' ' || 'alter system kill session ''' || sess || ''' immediate;');
 exception when no_data_found
 then continue;
 end;
 end loop;
 end;
 /
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: