Tuesday, May 8, 2012

Script ro Report Current Internal Locks

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
/