How to find dead locks ?
select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;
How to find information regarding BLOCKING SESSION ?
select sid, event, username,machine , program from v$session
where sid in (select blocking_session from gv$session where blocking_session is not null);
Find SQL using SID ?
select s.sid, q.sql_text from gv$sqltext q, gv$session s
where q.address = s.sql_address and s.sid = 130 order by piece;
How to kill a seesion ?
select SID, SERIAL# from v$session;
alter system kill session '29, 63' immediate;
select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;
How to find information regarding BLOCKING SESSION ?
select sid, event, username,machine , program from v$session
where sid in (select blocking_session from gv$session where blocking_session is not null);
Find SQL using SID ?
select s.sid, q.sql_text from gv$sqltext q, gv$session s
where q.address = s.sql_address and s.sid = 130 order by piece;
How to kill a seesion ?
select SID, SERIAL# from v$session;
alter system kill session '29, 63' immediate;
No comments:
Post a Comment