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;
/
DBA Knowledge Base 