grant alter system to sa;
grant select on v_$lock to sa;
grant select on all_objects to sa;
create or replace procedure D_debug
as
v_index varchar2(40);
v_table varchar2(40);
v_sql varchar2(100);
cursor c_fk is
select sid, serial#
from v$session
where sid in (select a.sid from v$lock a, all_objects b
where type = 'TM'
and a.id1 = b.object_id);
begin
open c_fk;
loop
fetch c_fk into v_table,v_index;
exit when c_fk%notfound;
v_sql:='alter system kill SESSION ('||v_table||', '||v_index||')';
execute immediate v_sql;
end loop;
close c_fk;
end;
oracle中查看表是否被锁
查看表是否被锁
SELECT
a.sid, b.owner, object_name, object_type
FROM v$lock a, all_objects b
WHERE TYPE = 'TM'
and a.id1 = b.object_id;
查到的都是被锁的表
这样可以把它杀掉
SELECT sid,serial# FROM v$session WHERE sid = &sid;
alter system kill session ‘sid,serial#’;
合成查询
SELECT
sid,
serial#
FROM
v$session
WHERE
sid in (SELECT
a.sid
FROM
v$lock a,
all_objects b
WHERE
TYPE = 'TM' AND
a.id1 = b.object_id
)