This script reports on internal locks (latches) held in the database, the type of lock and the locking mode that the lock is held in.
REM LOCATION: Database Tuning\Contention Reports
REM FUNCTION: Document current internal locks
REM TESTED ON: 7.3.3.5, 8.0.4.1, 8.1.5, 8.1.7, 9.0.1, 10.2.0.3, 11.1.0.6
REM PLATFORM: non-specific
REM REQUIRES: sys.dba_lock_internal, sys.v_$session
REM
REM
REM******************************* Oracle Administration ********************
COLUMN username FORMAT a10 HEADING 'Lock|Holder'
COLUMN session_id HEADING 'User|SID'
COLUMN lock_type FORMAT a27 HEADING ' Lock Type'
COLUMN mode_held FORMAT a10 HEADING ' Mode| Held'
COLUMN mode_requested FORMAT a10 HEADING ' Mode| Requested'
COLUMN lock_id1 FORMAT a30 HEADING ' Lock/Cursor| ID1'
COLUMN lock_id2 FORMAT a10 HEADING ' Lock| ID2'
PROMPT 'When prompted, enter the lock type and lock mode to report'
PROMPT 'You can also enter ALL for all lock types and lock modes'
SET LINES 132 PAGES 59 FEEDBACK OFF ECHO OFF VERIFY OFF
BREAK ON username
TTITLE left _date center 'Report on Internal Locks' skip 2
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 UPPER (mode_held) LIKE UPPER ('%&&mode%')
OR UPPER ('&&mode') = 'ALL' AND UPPER (lock_type) LIKE
UPPER ('%&&lock%')
OR UPPER (mode_held) LIKE UPPER ('%&&mode%')
OR UPPER ('&&mode') = 'ALL'
AND UPPER ('&&lock') = 'ALL'
AND a.session_id = b.SID
AND ROWNUM < 3
ORDER BY 1, 2
/
