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;
 /