使用语句: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;