【Oracle】查询锁的相关SQL

--查看有锁的进程


select t2.username,t2.sid,t2.serial#,t2.logon_time,t2.state
from v$locked_object t1,v$session t2 
where t1.session_id=t2.sid order by t2.logon_time desc



--杀掉进程
alter system kill session 'sid,serial#';   

--或者：
select 'alter system kill session '||chr(39)||t2.sid||chr(44)||t2.serial#||chr(39)||';',t2.logon_time
from v$locked_object t1,v$session t2 
where t1.session_id=t2.sid order by t2.logon_time 


--查看相关的服务器对应的锁及对象
select object_name, machine, s.sid,s.serial#,s.LOGON_TIME,s.STATE
from v$locked_object l, dba_objects o, v$session s
where l.object_id = o.object_id
and l.session_id =s.sid;


--查出系统中的pid
select spid, osuser, s.program
from v$session s, v$process p
where s.paddr = p.addr
and s.sid =&sid;


或者

select object_name as 对象名称,s.sid,s.serial#,p.spid as 系统进程号
from v$locked_object l , dba_objects o , v$session s , v$process p
where l.object_id=o.object_id and l.session_id=s.sid and s.paddr=p.addr;