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:
SIDSERIAL# PROCESS USERNAME TERMINAL
-- -- --
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
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)
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
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',
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" p
