RE: Information about User-defined Locks

From: Jon Walthour <jonw_at_fuse.net>
Date: Wed, 25 Jul 2001 05:25:06 -0700
Message-ID: <F001.0035447A.20010725053025@fatcity.com>

Listers:

Solved my own problem and thought I would share the solution with you all.

To find the name of a user-defined lock:

SELECT s.sid

, s.serial#
, DECODE(

              s.process
            , NULL, DECODE(SUBSTR(p.username, 1, 1), '?', UPPER(s.osuser),
p.username)
            , DECODE(p.username, 'oracusr ', LOWER(s.osuser),
s.process)) AS process


       , NVL(s.username, 'SYS (' || LOWER(bg.name) || ')') AS

username


       , DECODE(s.username, NULL, ' ', DECODE(s.terminal, NULL,

RTRIM(LOWER(p.terminal), CHR(0)), LOWER(s.terminal))) AS terminal


       , 'UL' as type

       , DECODE(l.lmode, 0, 'none', 1, 'null', 2, 'RS', 3, 'RX',

4, 'S', 5, 'SRX', 6, 'X', 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


       , 'lock name: ' || la.name || ', expiration date: ' ||

TO_CHAR(la.expiration, 'DD-MON-RR HH24:MI:SS') as detail

    FROM sys.v_$lock l


       , sys.v_$session s

       , sys.v_$process p

       , sys.v_$bgprocess bg

       , sys.dbms_lock_allocated la

   WHERE l.sid = s.sid
     AND s.paddr = bg.paddr(+)
     AND s.paddr = p.addr(+)
     AND l.id1 = la.lockid(+)
     AND l.type = 'UL';

Thanks, everyone, for your continued help through this forum.

Jon Walthour

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jon Walthour
  INET: jonw_at_fuse.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Wed Jul 25 2001 - 07:25:06 CDT