oracle 解锁表

操作的前提 用 sys 用户 以 SYSDBA 角色登录

第一种解锁方式

1.查询被锁的表

select object_name,machine,s.sid,s.serial#
from v$locked_object l,dba_objects o ,v$session s
where l.object_id　=　o.object_id and l.session_id=s.sid;
1
2
3

2.查询那个session引起表被锁

select b.username,b.sid,b.serial#,logon_time
from v$locked_object a,v$session b
where a.session_id = b.sid order by b.logon_time；
1
2
3

select object_name,machine,s.sid,s.serial#
from v$locked_object l,dba_objects o ,v$session s
where l.object_id　=　o.object_id and l.session_id=s.sid;

1
2
3
4

3.解锁表

ALTER system kill session '614, 50281';
1

第二种解锁表的方式

1. 查看被锁的表

SELECT p.spid, a.serial#, c.object_name, b.session_id, b.oracle_username,b.os_user_name
 FROM v$process p, v$session a, v$locked_object b, all_objects c
 WHERE p.addr = a.paddr 
 AND a.process = b.process
 AND c.object_id = b.object_id;
1
2
3
4
5

2.批量解锁

declare cursor mycur is   
select b.sid,b.serial#   
　　from v$locked_object a,v$session b   
　　where a.session_id = b.sid group by b.sid,b.serial#;   
   
begin   
  for cur in mycur   
    loop     
     execute immediate ( 'alter system  kill session  '''||cur.sid || ','|| cur.SERIAL# ||''' ');   
     end loop;   
end;
1
2
3
4
5
6
7
8
9
10
11

 
1

觉得文章实用，请在右上方点个赞

 
1