DBA_BLOCKERS – Shows non-waiting sessions holding locks being waited-on
DBA_DDL_LOCKS – Shows all DDL locks held or being requested
DBA_DML_LOCKS – Shows all DML locks held or being requested
DBA_LOCK_INTERNAL – Displays 1 row for every lock or latch held or being requested with the username of who is holding the lock
DBA_LOCKS – Shows all locks or latches held or being requested
DBA_WAITERS – Shows all sessions waiting on, but not holding waited for locks
V$LOCK – view lists the locks currently held within the Oracle server, and outstanding requests for a lock or latch.
V$LOCKED_OBJECT – view combines session, rollback, and locked object information for locks acquired by transactions in the system.

Query to show locks for a specific user

SELECT
NVL(b.username,’SYS’) username,
session_id,lock_type,mode_held,
mode_requested,lock_id1,lock_id2
FROM
sys.dba_lock_internal a,
sys.v_$session b
WHERE …

Query using v$process and v$locked_object to see specific locks

SELECT s.sid, s.serial#, p.spid
FROM
v$session s,
v$process p
WHERE
s.paddr = p.addr
AND
s.sid IN (SELECT SESSION_ID FROM v$locked_object);

Query to Show locked objects

SET lines 100 pages 999
col username format a20
col sess_id format a10
col object format a25
col mode_held format a10
SELECT oracle_username || ‘ (‘ || s.osuser || ‘)’ username
, s.sid || ‘,’ || s.serial# sess_id
, owner || ‘.’ || object_name object
, object_type
, DECODE( l.block
, 0, ‘Not Blocking’
, 1, ‘Blocking’
, 2, ‘Global’) status
, DECODE(v.locked_mode
, 0, ‘None’
, 1, ‘Null’
, 2, ‘Row-S (SS)’
, 3, ‘Row-X (SX)’
, 4, ‘Share’
, 5, ‘S/Row-X (SSX)’
, 6, ‘Exclusive’, TO_CHAR(lmode)) mode_held
FROM v$locked_object v
, dba_objects d
, v$lock l
, v$session s
WHERE v.object_id = d.object_id
AND v.object_id = l.id1
AND v.session_id = s.sid
ORDER BY oracle_username
, session_id

Comments are closed.