select /+rule/s.sid,p.spid,l.type,round(max(l.ctime)/60,0) lock_min,s.sql_id,s.USERNAME,b.owner,b.object_type,b.object_name
from v$session s, v$process p,v$lock l,v$locked_object o,dba_objects b
where o.SESSION_ID=s.sid and s.sid=l.sid and o.OBJECT_ID=b.OBJECT_ID
and s.paddr = p.addr and l.ctime >100 and l.type in ('TX','TM','FB')
group by s.sid,p.spid,l.type,s.sql_id,s.USERNAME,b.owner,b.object_type,b.object_name
order by 9,1,3;
select sid,serial#,username,machine,blocking_session from v$session where sid=1; -- 查看哪台电脑导致的锁
-- 数据库服务器上执行
su - oracle
ps -ef|grep 4319 -- spid
kill -9 4319 -- spid
-- 查看语句
SELECT a.username,a.machine, b.sql_id, b.SQL_FULLTEXT, 'alter system kill session '''||a.sid||','||a.serial#||''';'
FROM v$session a, v$sqlarea b
WHERE a.sql_address = b.address
AND a.SQL_HASH_VALUE = b.HASH_VALUE;
alter system kill session '1,18'; -- 根据查出来的sid 和serial# 能确定是谁了然后kill 掉
