Script ro Report Current Internal Locks

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

/