oracle删除阻塞,oracle阻塞处理

--查看等待的session

select * from dba_waiters;

--查看锁等待情况

select distinct lk.SID,lk.ID1,lk.ID2,lk.LMODE,lk.REQUEST,lk.BLOCK,lk.TYPE,do.owner || '.' || do.object_name

from v$lock lk,

v$locked_object lo,

dba_objects do

where do.object_id = lo.OBJECT_ID

and lo.SESSION_ID = lk.SID order by sid;

--根据session的SID查看阻塞会话的详细信息

select  p.SPID,

s.username,

s.machine,

s.program,

s.sid,

s.serial#,

s.status,

c.piece,

c.sql_text

from v$session s, v$process p,v$sqltext c where sid in(27，1,34) and p.addr = s.paddr and s.sql_address=c.address(+);

--杀掉阻塞的会话

alter system kill session 'sid,serial#';

批量生成杀掉session的语句：

SELECT'alter system kill session '''|| c.sid ||''||','|| c.serial# ||''';',

a.object_id, a.session_id, b.object_name, c.*

FROM v$locked_object a, dba_objects b, v$session c

WHERE a.object_id = b.object_id

AND a.SESSION_ID = c.sid(+)

ORDER BY logon_time;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30373263/viewspace-2124887/，如需转载，请注明出处，否则将追究法律责任。