| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re[2]: Information about User-defined Locks
Jon,
User defined locks do not lock anything, except a very small chunk of the SGA, part of the shared pool. Now the question is: is the procedure hanging while holding the UL or while trying to acquire the UL? A UL must be acquired by using the DBMS_LOCK package, namely the ALLOCATE_UNIQUE and REQUEST procedure/functions. Therefore there must be a snippet of code like the following somewhere in the procedure:
BEGIN
DBMS_LOCK.ALLOCATE_UNIQUE(:server_lock_name, :server_lock_handle);
:server_lock_allocate := DBMS_LOCK.REQUEST (:server_lock_handle,
DBMS_LOCK.X_MODE,1,
FALSE);
END;
If you can find the lock request code, look around to see if there is a
DBMS_LOCK.CONVERT statement afterwards, which could cause the problem, or what
other objects is the procedure trying to get at that may have been locked from
elsewhere.
Dick Goulet
PS: You may find that the amount of time the REQUEST function is set to is 'infinity' namely 864000000 seconds which is the default. If so put something reasonable in there and then check the status returned. In the example the amount of time I'll wait is 1 second. Now IF I get the lock the :server_lock_allocate = 0, else it = 1.
____________________Reply Separator____________________ Author: "Viraj Luthra" <viraj999_at_lycos.com> Date: 7/24/2001 5: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: 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:09:37 CDT
 |
 |