Basics of Locks and Blocking Session in Oracle 19c

Basics of Locks and Blocking Session in Oracle 19c

-- Blocking Session in the Database --
SET lines 300
col wait_class for A20
col event for A35
SELECT blocking_session, sid, serial#, wait_class, event, seconds_in_wait
FROM v$session
WHERE blocking_session is not NULL
ORDER by blocking_session;

-- Get the types of locks by the Blocking Session
SET LINES 300
COL username FOR A30
SELECT l.sid, s.username, l.type, l.lmode, l.ctime, l.id1, l.id2
FROM v$lock l, v$session s
WHERE l.sid = s.sid and s.sid = &sid
AND s.username is not null;

-- Object Detail
set lines 300
col owner for A16
col object_name for A20
select owner, object_type, object_name, object_id from dba_objects where object_id=&object_id; -- ID1 from Previous Query