查询锁的有用语句，适用于10g以上
select *
from gv$lockwhere (id1, id2, type) in
(select id1, id2, type from gv$lock where request > 0);
select inst_id, sid, blocking_instance, blocking_session
from gv$session
where blocking_instance is not null
and blocking_session is not null
order by 1, 2;
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)
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';
LOCK_TREE
---------------------------------------------
2.1107
1.1252
1.1113
1.1158
2.1011
2.633
1.1299
1.1447
1.388
2.1348
2.392
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)
select distinct blocker from lk where blocker not in (select waiter from lk)
)
loop
select regexp_substr(i.blocker,'[0-9]+$')||','||serial# ||',@' || regexp_substr(i.blocker,'[0-9]+'),
substr(username||':'||program,1,29) into sess, sessinfo
from gv$session where inst_id = regexp_substr(i.blocker,'[0-9]+') and sid = regexp_substr(i.blocker,'[0-9]+$');
dbms_output.put_line(sessinfo || ' ' || 'alter system kill session ''' || sess || ''' immediate;');
end loop;
end;
/
