Re: Information about User-defined Locks

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