Oracle查阻塞会话源头

https://www.cnblogs.com/lijiaman/p/12961540.html

--使用v$session来查看单实例的阻塞session信息

SELECT LPAD(' ',5*(LEVEL-1))||S."USERNAME" AS user_name ,
LPAD(' ',5*(LEVEL-1))||S."SID" AS session_id,
S."SERIAL#",
S."SQL_ID", S."ROW_WAIT_OBJ#",
S."WAIT_CLASS",
S."EVENT",
S."P1",
S."P2",
S."P3",
S."SECONDS_IN_WAIT"
FROM V$SESSION S
WHERE S."BLOCKING_SESSION" IS NOT NULL
OR S.SID IN(SELECT DISTINCT BLOCKING_SESSION FROM V$SESSION)
START WITH S."BLOCKING_SESSION" IS NULL
CONNECT BY PRIOR S."SID" = S."BLOCKING_SESSION";

--使用v$session来查看RAC数据库和单实例阻塞session信息

SELECT
LPAD(' ',5*(LEVEL-1))||S."USERNAME" ,
LPAD(' ',5*(LEVEL-1))||S."INST_ID"||','||S."SID" ,
S."SERIAL#" ,
S."SQL_ID",
S."ROW_WAIT_OBJ#",
S."WAIT_CLASS",
S."EVENT",
S."P1",
S."P2",
S."P3",
S."SECONDS_IN_WAIT",
s."BLOCKING_INSTANCE"||','||s."BLOCKING_SESSION"
FROM GV$SESSION S
WHERE S."BLOCKING_SESSION" IS NOT NULL
OR (S."INST_ID"||','||S."SID") IN(SELECT DISTINCT BLOCKING_INSTANCE||','||BLOCKING_SESSION FROM GV$SESSION)
START WITH (s."BLOCKING_INSTANCE"||','||s."BLOCKING_SESSION") = ','
CONNECT BY PRIOR (S."INST_ID"||','||S."SID") = (s."BLOCKING_INSTANCE"||','||s."BLOCKING_SESSION");

select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@'|| s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from gv$lock l1, gv$session s1, gv$lock l2, gv$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 ;


select l1.sid, ' IS BLOCKING ', l2.sid
from gv$lock l1, gv$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2;


select
blocksession.sid as block_session_sid,
blocksession.serial# as block_session_serial#,
blocksession.username as block_session_username,
blocksession.osuser as block_session_osuser,
blocksession.machine as block_session_machine,
blocksession.status as block_session_status,
blockobject.object_name as blocked_table,
waitsession.sid as wait_session_sid,
waitsession.serial# as wait_session_serial#,
waitsession.username as wait_session_username,
waitsession.osuser as wait_session_osuser,
waitsession.machine as wait_session_machine,
waitsession.status as wait_session_status
from
v$lock blocklock,
v$lock waitlock,
v$session blocksession,
v$session waitsession,
v$locked_object lockedobject,
dba_objects blockobject
where
blocklock.block = 1
and blocklock.sid != waitlock.sid
and blocklock.id1 = waitlock.id1
and blocklock.id2 = waitlock.id2
and blocklock.sid = blocksession.sid
and waitlock.sid = waitsession.sid
and lockedobject.session_id = blocksession.sid
and lockedobject.object_id = blockobject.object_id;


ALTER SYSTEM KILL SESSION '113,55609';