Hello gurus,
I have re-compiled a lot of sql queries to make this query to get the locking
contention. It works and tells me every thing I want to know except, that I want to
know the sql text also associated with the lock.
Now I am not able to join the v$sqltext, for some reason, it gives shitty data when i
try to do that. Can any one please, please help me out? I need this like asap.
I am attaching the script, called lock.sql
Thanks and regards,
raja
Get 250 color business cards for FREE!
http://businesscards.lycos.com/vp/fastpath/
select OS_USER_NAME as OSUSER,s.serial# as "SERIAL NO.",
PROCESS as "PID",
ORACLE_USERNAME as "USERNAME" ,
l.SID as "SID",
DECODE(l.TYPE
-- Long locks
, 'TM', 'dml/data enq (TM)'
, 'TX', 'transac enq (TX) '
, 'UL', 'pls usr lock (UL)'
-- Short locks
, 'BL', 'buf hash tbl (BL)'
, 'CF', 'control file (CF)'
, 'CI', 'cross inst f (CI)'
, 'CU', 'cursor bind (CU) '
, 'DF', 'data file (CF) '
, 'DL', 'direct load (DL) '
, 'DM', 'mount/strtup (DM)'
, 'DR', 'reco lock (DR) '
, 'DX', 'distrib tran (DX)'
, 'FI', 'sga opn file (FI)'
, 'FS', 'file set (FS) '
, 'IN', 'instance num (IN)'
, 'IR', 'instce recvr (IR)'
, 'IS', 'get state (IS) '
, 'IV', 'libcache inv (IV)'
, 'JQ', 'job queue (JQ) '
, 'KK', 'log sw kick (KK) '
, 'LS', 'log switch (LS) '
, 'MM', 'mount def (MM) '
, 'MR', 'media recvry (MR)'
, 'PF', 'pwfile enq (PF) '
, 'PR', 'process strt (PR)'
, 'RW', 'row wait (RW) '
, 'RT', 'redo thread (RT) '
, 'SC', 'scn enq (SC) '
, 'SM', 'smon lock (SM) '
, 'SN', 'seqno instce (SN)'
, 'SQ', 'seqno enq (SQ) '
, 'ST', 'space transc (ST)'
, 'SV', 'seqno value (SV) '
, 'TA', 'generic enq (TA) '
, 'TD', 'dll enq (TD) '
, 'TE', 'extend seg (TE) '
, 'TS', 'temp segment (TS)'
, 'TT', 'temp table (TT) '
, 'UN', 'user name (UN) '
, 'WL', 'write redo (WL) '
, 'TYPE = ' || l.TYPE) AS type ,
DECODE(l.lmode, 0, 'none', 1, 'null', 2, 'RS', 3, 'RX', 4, 'S', 5, 'SRX', 6,
'Exclusive', TO_CHAR(l.lmode)) AS lmode ,
DECODE(l.request, 0, 'none', 1, 'null', 2, 'RS', 3, 'RX', 4, 'S', 5, 'SRX',
6, 'X', TO_CHAR(l.request)) AS lrequest,
decode(BLOCK,
0, 'Not Blocking',
1, 'Blocking',
2, 'Global', block) as "DETAIL",
OWNER,
OBJECT_NAME
from sys.v_$locked_object lo, dba_objects do, sys.v_$lock l,
(SELECT a.sid, a.serial#
FROM v$session a, v$bgprocess b
WHERE a.paddr = b.paddr(+)) s
where lo.OBJECT_ID = do.OBJECT_ID
AND l.SID = lo.SESSION_ID
and s.sid = l.sid
/
