Embedded Files
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
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) featurePerformance Hub
TODO
Bibliography
Bibliography
Page updated
Google Sites
Report abuse