Re:RE: Information about User-defined Locks

From: <dgoulet_at_vicr.com>
Date: Wed, 25 Jul 2001 07:30:56 -0700
Message-ID: <F001.003547BA.20010725065646@fatcity.com>

Jon,

    Your right, thanks. But for the edification of others who may not be using user defined locks today, here is the results of Jon's query from one of my DB's that does use them:

       SID    SERIAL# PROCESS         USERNAME                       TERMINAL
---------- ---------- --------------- ------------------------------ ----------

TY LMODE

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

LREQUEST

---

DETAIL

---

        45      39789 169:137         WORKENTRY                      ora_upload

UL X
none
lock name: DlogSrv1, expiration date: 02-AUG-01 17:02:38

        55      48632 81:67           WORKENTRY                      ora_upload

UL X
none
lock name: DlogSrv0, expiration date: 02-AUG-01 17:02:37

Sorry about the mess this Lotus CCMail package makes of things.

Dick Goulet

____________________Reply Separator____________________
Author: "Jon Walthour" <jonw_at_fuse.net>
Date:       7/25/2001 5:30 AM

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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: dgoulet_at_vicr.com

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 - 09:30:56 CDT