Lock Query details:
It provided Locking details along with more details.
PROMPT Lock Query details
col process for
a15
col terminal for
a25
col lmode for a20
col object for
a30
set lin 1000
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.osuser, 'SYS
('||substr(p.username,1,4)||')') username,
decode(s.terminal, null,
rtrim(p.terminal, chr(0)),
upper(s.terminal)) terminal,
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,
decode(l.type,
-- Long locks major
'TM', 'DML/DATA
ENQ', 'TX', 'TRANSAC ENQ',
'UL', 'PLS USR LOCK',
-- Short locks minor
'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
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
SQL ***/
select s.sid,
s.serial#, s.process, s.osuser, 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 SQL
***/
select s.sid,
s.serial#, s.process, s.osuser, 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
/
No comments:
Post a Comment