Blocking Sessions
TODO: Scripts untested
SELECT inst_id,
sid,
type,
lmode,
request,
ctime,
block
FROM v$lock;
SELECT blocking_session,
sid,
wait_class,
seconds_in_wait
FROM v$session
WHERE blocking_session IS NOT NULL
ORDER BY blocking_session;
SELECT l1.inst_id,
l1.sid,
' IS BLOCKING ',
l2.sid,
l1.type,
l2.type,
l1.lmode,
l2.lmode,
l2.inst_id
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;
SET LINESIZE 200
SET PAGESIZE 9999
COLUMN inst_id FORMAT a10
COLUMN serial# FORMAT a10
COLUMN machine FORMAT a30
COLUMN username FORMAT a10
COLUMN event FORMAT a20
COLUMN blocking_session FORMAT 999999
COLUMN blocking_instance FORMAT 999999
COLUMN status FORMAT a10
COLUMN INST_ID FORMAT 9999
COLUMN SERIAL# FORMAT 999999
SELECT inst_id,
sid,
serial#,
machine,
username,
event,
blocking_session,
blocking_instance,
status,
sql_id
FROM gv$session
WHERE status = 'ACTIVE'
AND username IS NOT NULL;
SELECT 'Instance ' ||
s1.INST_ID ||
' ' ||
s1.username ||
'@' ||
s1.machine ||
' ( SID=' ||
s1.sid ||
',' ||
s1.serial# ||
s1.status ||
' ) is blocking ' ||
s2.username ||
'@' ||
s2.machine ||
' ( SID=' ||
s2.sid ||
' ) ' ||
s2.sql_id
FROM gv$lock l1,
gv$session s1,
gv$lock l2,
gv$session s2
WHERE s1.sid=l1.sid
AND s1.inst_id=l1.inst_id
AND s2.sid=l2.sid
AND s2.inst_id=l2.inst_id
AND l1.BLOCK=1
AND l2.request > 0
AND l1.id1 = l2.id1
AND l2.id2 = l2.id2 ;
SELECT sql_id
FROM v$session
WHERE sid=&sid;
SELECT sql_fulltext
FROM v$sql
WHERE sql_id = '&sqlid';
Enterprise Manager
Targets - Databases - Choose your database - Performance - Blocking Sessions
Targets
Databases
Choose your database
Oracle Database
Monitoring
All Metrics
User Block Chain
Blocking Session DB Time
Performance Hub
This is an Oracle Cloud Infrastructure (OCI) featureTODO