--查看被锁对象
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/