oracle中查看和刪除被鎖表

 

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
            )