Oracle 鎖表解決方案

--查看被鎖對象

select a.session_id,a.object_id,a.oracle_username,b.object_type,b.object_name from v$locked_object a,all_objects b

where a.object_id = b.object_id;

--解鎖辦法

1)查到sid、serial#

select c.sid,c.serial# from v$session c where c.sid in(select a.session_id  from  v$locked_object a,all_objects b where a.object_id = b.object_id );

2)解鎖

alter system kill session ‘sid,serial#’;

對於上述方法kill session無效,報ora錯誤時,用以下辦法解決:

1)、--找出spid

select spid, osuser, s.program

from v$session s,v$process p

where s.paddr=p.addr and s.sid = 'xxx'--由上述查鎖sql可以得到

2)、用root使用者登入oracle資料庫的伺服器,殺掉進程馬上解鎖

kill -9 spid;

附上2段自動解鎖sql:

--僅適用單個sid

declare

v_sid number;

v_serial number;

vv_sql varchar2(4000);

begin

select c.sid,c.serial# into v_sid,v_serial from v$session c

where c.sid in

(select a.session_id  from  v$locked_object a,all _objects b where a.object_id = b.object_id );

dbms_output.put_line(v_sid||','||v_serial);

vv_sql :='alter system kill session '||''''||v_sid||','||v_serial||'''';

execute immediate vv_sql;

end;

--遊標遍曆適用多個sid

declare

begin

for cur in

(select c.sid,c.serial# from v$session c

where c.sid in

(select a.session_id  from  v$locked_object a,all_objects b where a.object_id = b.object_id )) loop

execute immediate 'alter system kill session '||''''||cur.sid||','||cur.serial#||'''';

end loop;

end;

本欄目更多精彩內容：http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/