Wednesday, 11 July 2012

Script to Report Database Locks

Script to Report Database Locks

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:,, 8.1.5, 9.0.1,,
REM PLATFORM:   non-specific
REM REQUIRES:   sys.v_$session, sys.dba_locks
REM******************** Oracle Administration *************************

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
CLEAR columns
SET feedback on echo on pages 22 lines 80

No comments:

Post a Comment