LOCKS

LOCKS
Description

This script lists details of all current database locks on the system, 

who is holding the lock, and the object name where the lock is held.


------------------------------------------------------
set pages 2000
col username form A10
col sid form 9990
col type form A4
col lmode form a10
col request form a20
col objname form A30 Heading "Object Name"
rem Display the object id's if the object_name is not unique
rem col id1 form 999999900   
rem col id2 form 999999900

SELECT sn.username, m.sid, m.type,
   DECODE(m.lmode, 0, 'None'
                 , 1, 'Null'
                 , 2, 'Row Share'
                 , 3, 'Row Excl.'
                 , 4, 'Share'
                 , 5, 'S/Row Excl.'
                 , 6, 'Exclusive'
                 , lmode, ltrim(to_char(lmode,'990'))) lmode,
   DECODE(m.request, 0, 'None'
                 , 1, 'Null'
                 , 2, 'Row Share'
                 , 3, 'Row Excl.'
                 , 4, 'Share'
                 , 5, 'S/Row Excl.'
                 , 6, 'Exclusive'
                 , request, ltrim(to_char(request,'990'))) request,
         obj1.object_name objname, obj2.object_name objname
FROM v$session sn, V$lock m, dba_objects obj1, dba_objects obj2
WHERE sn.sid = m.sid
AND m.id1 = obj1.object_id (+)
AND m.id2 = obj2.object_id (+)
     AND lmode != 4 
ORDER BY id1,id2, m.request
/

----------------------------------------------------------------

 COLUMN lock_id2 FORMAT A30
 
 select to_char(SESSION_ID,'999') sid , 
    substr(LOCK_TYPE,1,30) Type, 
    substr(lock_id1,1,45) Object_Name, 
    substr(mode_held,1,4) HELD, 
    substr(mode_requested,1,4) REQ, 
    lock_id2 lock_addr
 FROM dba_lock_internal
 WHERE 
    mode_requested < 'None' 
    and mode_requested < mode_held 
 ;

------------------------------------------------------------------

ALL THE LOCKED OBJECTS , AND THE USER THAT IS USING IT:
------------------------------------------------------------------

COLUMN USER FORMAT A10;
COLUMN OBJETO FORMAT A10;
SELECT L.SID, S.USERNAME "USER", L.TYPE "TIPO", L.ID1, L.ID2, L.LMODE
"MODO", L.REQUEST "REQ", L.CTIME "TIEMPO", O.OBJECT_NAME "OBJETO"
FROM V$LOCK L, V$SESSION S, V$LOCKED_OBJECT B, OBJ O
WHERE L.SID = S.SID AND S.USERNAME <> 'OPS$PTRANS' AND L.SID = B.SESSION_ID AND
B.OBJECT_ID = O.OBJECT_ID
ORDER BY 1
/

--------------------------------------------------------------------

rem -----------------------------------------------------------------------
rem Filename:   lock.sql
rem Purpose:    Display database locks and latches (with tables names, etc)
rem -----------------------------------------------------------------------

set pagesize 23
set pause on
set pause 'Hit any key...'

col sid format 999999
col serial# format 999999
col username format a12 trunc
col process format a8 trunc
col terminal format a12 trunc
col type format a12 trunc
col lmode format a4 trunc
col lrequest format a4 trunc
col object format a73 trunc

select s.sid, s.serial#,
       decode(s.process, null,
          decode(substr(p.username,1,1), '?',   upper(s.osuser), p.username),
          decode(       p.username, 'ORACUSR ', upper(s.osuser), s.process)
       ) process,
       nvl(s.username, 'SYS ('||substr(p.username,1,4)||')') username,
       decode(s.terminal, null, rtrim(p.terminal, chr(0)),
              upper(s.terminal)) terminal,
       decode(l.type,
          -- Long locks
                      'TM', 'DML/DATA ENQ',   'TX', 'TRANSAC ENQ',
                      'UL', 'PLS USR LOCK',
          -- Short locks
                      'BL', 'BUF HASH TBL',  'CF', 'CONTROL FILE',
                      'CI', 'CROSS INST F',  'DF', 'DATA FILE   ',
                      'CU', 'CURSOR BIND ',
                      'DL', 'DIRECT LOAD ',  'DM', 'MOUNT/STRTUP',
                      'DR', 'RECO LOCK   ',  'DX', 'DISTRIB TRAN',
                      'FS', 'FILE SET    ',  'IN', 'INSTANCE NUM',
                      'FI', 'SGA OPN FILE',
                      'IR', 'INSTCE RECVR',  'IS', 'GET STATE   ',
                      'IV', 'LIBCACHE INV',  'KK', 'LOG SW KICK ',
                      'LS', 'LOG SWITCH  ',
                      'MM', 'MOUNT DEF   ',  'MR', 'MEDIA RECVRY',
                      'PF', 'PWFILE ENQ  ',  'PR', 'PROCESS STRT',
                      'RT', 'REDO THREAD ',  'SC', 'SCN ENQ     ',
                      'RW', 'ROW WAIT    ',
                      'SM', 'SMON LOCK   ',  'SN', 'SEQNO INSTCE',
                      'SQ', 'SEQNO ENQ   ',  'ST', 'SPACE TRANSC',
                      'SV', 'SEQNO VALUE ',  'TA', 'GENERIC ENQ ',
                      'TD', 'DLL ENQ     ',  'TE', 'EXTEND SEG  ',
                      'TS', 'TEMP SEGMENT',  'TT', 'TEMP TABLE  ',
                      'UN', 'USER NAME   ',  'WL', 'WRITE REDO  ',
                      'TYPE='||l.type) type,
       decode(l.lmode, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
                       4, 'S',    5, 'RSX',  6, 'X',
                       to_char(l.lmode) ) lmode,
       decode(l.request, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
                         4, 'S', 5, 'RSX', 6, 'X',
                         to_char(l.request) ) lrequest,
       decode(l.type, 'MR', decode(u.name, null,
                            'DICTIONARY OBJECT', u.name||'.'||o.name),
                      'TD', u.name||'.'||o.name,
                      'TM', u.name||'.'||o.name,
                      'RW', 'FILE#='||substr(l.id1,1,3)||
                      ' BLOCK#='||substr(l.id1,4,5)||' ROW='||l.id2,
                      'TX', 'RS+SLOT#'||l.id1||' WRP#'||l.id2,
                      'WL', 'REDO LOG FILE#='||l.id1,
                      'RT', 'THREAD='||l.id1,
                      'TS', decode(l.id2, 0, 'ENQUEUE',
                                             'NEW BLOCK ALLOCATION'),
                      'ID1='||l.id1||' ID2='||l.id2) object
from   sys.v_$lock l, sys.v_$session s, sys.obj$ o, sys.user$ u,
       sys.v_$process p
where  s.paddr  = p.addr(+)
  and  l.sid    = s.sid
  and  l.id1    = o.obj#(+)
  and  o.owner# = u.user#(+)
  and  l.type   <> 'MR'
UNION ALL                          /*** LATCH HOLDERS ***/
select s.sid, s.serial#, s.process, s.username, s.terminal,
       'LATCH', 'X', 'NONE', h.name||' ADDR='||rawtohex(laddr)
from   sys.v_$process p, sys.v_$session s, sys.v_$latchholder h
where  h.pid  = p.pid
  and  p.addr = s.paddr
UNION ALL                         /*** LATCH WAITERS ***/
select s.sid, s.serial#, s.process, s.username, s.terminal,
       'LATCH', 'NONE', 'X', name||' LATCH='||p.latchwait
from   sys.v_$session s, sys.v_$process p, sys.v_$latch l
where  latchwait is not null
  and  p.addr      = s.paddr
  and  p.latchwait = l.addr
/
------------------------------------------------------

BLOCKS AND BLCOKER WHICH IS BLOCKING
--------------------------------------------------------
select (select username from v$session where sid = a.sid) blocker, a.sid, ' is blocking ',
(select username  from v$session where sid = b.sid) blockee , b.sid  from v$lock a , v$lock b where a.block = 1   and b.request > 0   and a.id1 = b.id1   and a.id2 = b.id2;
--------------------------------------------------------

LOCKED OBJECTS USING BY THE USER
--------------------------------------------------------

set lin 120
col object_name for a30
select 
  oracle_username
  os_user_name,
  locked_mode,
  object_name,
  object_type
from 
  v$locked_object a,dba_objects b
where 
  a.object_id = b.object_id;

----------------------------------------------------------