Database locks are particularly troublesome when they are blocking other sessions. This report reports on database locking.
REM LOCATION: Database Tuning\Contention Reports
REM FUNCTION: Report all DB locks
REM TESTED ON: 7.3.3.5, 8.0.4.1, 8.1.5, 9.0.1, 10.2.0.3, 11.1.0.6
REM PLATFORM: non-specific
REM REQUIRES: sys.v_$session, sys.dba_locks
REM
REM
REM******************** Oracle Administration *************************
REM
COLUMN osuser format a15 heading 'User'
COLUMN session_id heading 'SID'
COLUMN mode_held format a20 heading 'Mode|Held'
COLUMN mode_requested format a20 heading 'Mode|Requested'
COLUMN lock_id1 format a10 heading 'Lock|ID1'
COLUMN lock_id2 format a10 heading 'Lock|ID2'
COLUMN type heading 'Type|Lock'
SET feedback off echo off pages 59 lines 131
TTITLE left _date center 'Report on All Locks' skip 2
SELECT NVL (a.osuser, 'SYS') osuser, b.session_id, TYPE, mode_held,
mode_requested, lock_id1, lock_id2
FROM v$session a, dba_locks b
WHERE a.sid = b.session_id
ORDER BY 2
/
CLEAR columns
SET feedback on echo on pages 22 lines 80
