oracle锁表

使用语句：alter session enable parallel dml;会锁表，只能执行一个窗口中的sql语句，想执行update、delete、insert操作的其它session_id只能等待，小心使用

1). 查找出被锁的表

select b.owner,b.object_name,a.session_id,a.locked_mode 
from v$locked_object a,dba_objects b 
where b.object_id = a.object_id;

 

或者：

select ls.osuser os_user_name,
ls.username user_name,
ls.type lock_type,
o.object_name object,
decode(ls.lmode,1,null,2,'Row Share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive',null) lock_mode,
o.owner,
ls.sid,
ls.serial# serial_num,
ls.id1,ls.id2,
ls.paddr,
ls.machine
 from sys.dba_objects o,
(select s.osuser,s.username,l.type,s.paddr,l.lmode,s.sid,s.serial#,l.id1,l.id2,s.MACHINE  from v$session s,v$lock l where s.sid=l.sid) ls
where o.object_id=ls.id1 and o.owner<>'SYS' order by o.owner,o.object_name

2).查找session_id和spid

　　查找session_id：

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;

 

　　查找spid：

select a.spid,a.username,b.program  from v$process a,v$session b where a.addr=b.paddr and a.addr='000007FF2DC8E578';

 

3). 杀进程中的会话和进程id

#掉进程 sid,serial#
alter system kill session "sid,serial#";
orakill sid spid;