Re: Information about User-defined Locks

Viraj:

Thanks for the idea, but I already know the lock type to be User-defined. I also know that p2 and p3 (id1 and id2) are application dependent, according to the documentation. So, joining them to dba_objects yields me nothing. But I appreciate your input ... let's keep looking.

Jon Walthour

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Tuesday, July 24, 2001 9:50 PM

> Jon,
>
> Try doing the following, it should give you the information, you need :-
>
> SELECT o.object_id, s.username, l.sid, object_name,
> DECODE( l.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',
> l.type ),
> DECODE( l.lmode,
> 0, 'None',
> 1, 'Null',
> 2, 'Row-s (SS)',
> 3, 'Row-x (SX)',
> 4, 'Share',
> 5, 'S/Row-X (SSX)',
> 6, 'Exclusive',
> to_char(l.request))
> FROM v$lock l, v$session s, dba_objects o
> WHERE s.sid = l.sid and
> l.id1 = o.object_id(+) and
> username is not null
> ORDER BY username, l.sid
> /
>
>
> --
>
> On Tue, 24 Jul 2001 16:45:47
> Jon Walthour wrote:
> >Listers:
> >
> >I had an interesting question today that I cannot find the answer for,
but would love to know about.
> >
> >A developer came to me today and asked how we could find the object that
was being locked by a User-defined lock (UL). He works with Oracle Clinical and in one of its "blackbox" procedures, it appears to hang while holding a UL. He would like to know what object it is holding. Now, as far as I know, in v$lock a UL's id1 and id2 do not provide useful information to this end; both parameters are "application dependent." So, where else can I turn?
> >
> >Jon Walthour
> >
>
>
> Get 250 color business cards for FREE!
> http://businesscards.lycos.com/vp/fastpath/
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Viraj Luthra
> INET: viraj999_at_lycos.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).

-- 
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).