--查看等待的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;
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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30373263/viewspace-2124887/