VIEW: SYS.DBA_LOCK_INTERNAL

Source

View Text - Preformatted

select
	sid session_id,
	decode(type,
		'MR', 'Media Recovery',
		'RT', 'Redo Thread',
		'UN', 'User Name',
		'TX', 'Transaction',
		'TM', 'DML',
		'UL', 'PL/SQL User Lock',
		'DX', 'Distributed Xaction',
		'CF', 'Control File',
		'IS', 'Instance State',
		'FS', 'File Set',
		'IR', 'Instance Recovery',
		'ST', 'Disk Space Transaction',
		'TS', 'Temp Segment',
		'IV', 'Library Cache Invalidation',
		'LS', 'Log Start or Switch',
		'RW', 'Row Wait',
		'SQ', 'Sequence Number',
		'TE', 'Extend Table',
		'TT', 'Temp Table',
		type) lock_type,
	decode(lmode,
		0, 'None',           /* Mon Lock equivalent */
		1, 'Null',           /* N */
		2, 'Row-S (SS)',     /* L */
		3, 'Row-X (SX)',     /* R */
		4, 'Share',          /* S */
		5, 'S/Row-X (SSX)',  /* C */
		6, 'Exclusive',      /* X */
		to_char(lmode)) mode_held,
         decode(request,
		0, 'None',           /* Mon Lock equivalent */
		1, 'Null',           /* N */
		2, 'Row-S (SS)',     /* L */
		3, 'Row-X (SX)',     /* R */
		4, 'Share',          /* S */
		5, 'S/Row-X (SSX)',  /* C */
		6, 'Exclusive',      /* X */
		to_char(request)) mode_requested,
         to_char(id1) lock_id1, to_char(id2) lock_id2,
	con_id
      from v$lock                /* processes waiting on or holding enqueues */
 union all                                          /* procs holding latches */
  select s.sid, 'LATCH', 'Exclusive', 'None', rawtohex(laddr), ' ', s.con_id
    from v$process p, v$session s, v$latchholder h
   where h.pid  = p.pid                       /* 6 = exclusive, 0 = not held */
    and  p.addr = s.paddr
 union all                                         /* procs waiting on latch */
  select sid, 'LATCH', 'None', 'Exclusive', rawtohex(latchwait), ' ', s.con_id
     from v$session s, v$process p
    where latchwait is not null
     and  p.addr = s.paddr
 union all                                            /* library cache locks */
  select  s.sid,
    decode(ob.kglhdnsp, 0, 'Cursor', 1, 'Table/Procedure/Type', 2, 'Body',
	     3, 'trigger', 4, 'Index', 5, 'Cluster', 13, 'Java Source',
             14, 'Java Resource', 32, 'Java Data', to_char(ob.kglhdnsp))
	  || ' Definition ' || lk.kgllktype,
    decode(lk.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
	   to_char(lk.kgllkmod)),
    decode(lk.kgllkreq,  0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
	   to_char(lk.kgllkreq)),
    decode(ob.kglnaown, null, '', ob.kglnaown || '.') || ob.kglnaobj ||
    decode(ob.kglnadlk, null, '', '@' || ob.kglnadlk),
    rawtohex(lk.kgllkhdl),
    s.con_id
   from v$session s, x$kglob ob, dba_kgllock lk
     where lk.kgllkhdl = ob.kglhdadr
      and  lk.kgllkuse = s.saddr

View Text - HTML Formatted

SELECT SID SESSION_ID
, DECODE(TYPE
, 'MR'
, 'MEDIA RECOVERY'
, 'RT'
, 'REDO THREAD'
, 'UN'
, 'USER NAME'
, 'TX'
, 'TRANSACTION'
, 'TM'
, 'DML'
, 'UL'
, 'PL/SQL USER LOCK'
, 'DX'
, 'DISTRIBUTED XACTION'
, 'CF'
, 'CONTROL FILE'
, 'IS'
, 'INSTANCE STATE'
, 'FS'
, 'FILE SET'
, 'IR'
, 'INSTANCE RECOVERY'
, 'ST'
, 'DISK SPACE TRANSACTION'
, 'TS'
, 'TEMP SEGMENT'
, 'IV'
, 'LIBRARY CACHE INVALIDATION'
, 'LS'
, 'LOG START OR SWITCH'
, 'RW'
, 'ROW WAIT'
, 'SQ'
, 'SEQUENCE NUMBER'
, 'TE'
, 'EXTEND TABLE'
, 'TT'
, 'TEMP TABLE'
, TYPE) LOCK_TYPE
, DECODE(LMODE
, 0
, 'NONE'
, /* MON LOCK EQUIVALENT */ 1
, 'NULL'
, /* N */ 2
, 'ROW-S (SS)'
, /* L */ 3
, 'ROW-X (SX)'
, /* R */ 4
, 'SHARE'
, /* S */ 5
, 'S/ROW-X (SSX)'
, /* C */ 6
, 'EXCLUSIVE'
, /* X */ TO_CHAR(LMODE)) MODE_HELD
, DECODE(REQUEST
, 0
, 'NONE'
, /* MON LOCK EQUIVALENT */ 1
, 'NULL'
, /* N */ 2
, 'ROW-S (SS)'
, /* L */ 3
, 'ROW-X (SX)'
, /* R */ 4
, 'SHARE'
, /* S */ 5
, 'S/ROW-X (SSX)'
, /* C */ 6
, 'EXCLUSIVE'
, /* X */ TO_CHAR(REQUEST)) MODE_REQUESTED
, TO_CHAR(ID1) LOCK_ID1
, TO_CHAR(ID2) LOCK_ID2
, CON_ID
FROM V$LOCK /* PROCESSES WAITING ON OR HOLDING ENQUEUES */ UNION ALL /* PROCS HOLDING LATCHES */ SELECT S.SID
, 'LATCH'
, 'EXCLUSIVE'
, 'NONE'
, RAWTOHEX(LADDR)
, ' '
, S.CON_ID
FROM V$PROCESS P
, V$SESSION S
, V$LATCHHOLDER H
WHERE H.PID = P.PID /* 6 = EXCLUSIVE
, 0 = NOT HELD */
AND P.ADDR = S.PADDR UNION ALL /* PROCS WAITING ON LATCH */ SELECT SID
, 'LATCH'
, 'NONE'
, 'EXCLUSIVE'
, RAWTOHEX(LATCHWAIT)
, ' '
, S.CON_ID
FROM V$SESSION S
, V$PROCESS P
WHERE LATCHWAIT IS NOT NULL
AND P.ADDR = S.PADDR UNION ALL /* LIBRARY CACHE LOCKS */ SELECT S.SID
, DECODE(OB.KGLHDNSP
, 0
, 'CURSOR'
, 1
, 'TABLE/PROCEDURE/TYPE'
, 2
, 'BODY'
, 3
, 'TRIGGER'
, 4
, 'INDEX'
, 5
, 'CLUSTER'
, 13
, 'JAVA SOURCE'
, 14
, 'JAVA RESOURCE'
, 32
, 'JAVA DATA'
, TO_CHAR(OB.KGLHDNSP)) || ' DEFINITION ' || LK.KGLLKTYPE
, DECODE(LK.KGLLKMOD
, 0
, 'NONE'
, 1
, 'NULL'
, 2
, 'SHARE'
, 3
, 'EXCLUSIVE'
, TO_CHAR(LK.KGLLKMOD))
, DECODE(LK.KGLLKREQ
, 0
, 'NONE'
, 1
, 'NULL'
, 2
, 'SHARE'
, 3
, 'EXCLUSIVE'
, TO_CHAR(LK.KGLLKREQ))
, DECODE(OB.KGLNAOWN
, NULL
, ''
, OB.KGLNAOWN || '.') || OB.KGLNAOBJ || DECODE(OB.KGLNADLK
, NULL
, ''
, '@' || OB.KGLNADLK)
, RAWTOHEX(LK.KGLLKHDL)
, S.CON_ID
FROM V$SESSION S
, X$KGLOB OB
, DBA_KGLLOCK LK
WHERE LK.KGLLKHDL = OB.KGLHDADR
AND LK.KGLLKUSE = S.SADDR