Locking contention query

Hello gurus,

I have re-compiled a lot of sql queries to make this query to get the locking contention. It works and tells me every thing I want to know except, that I want to know the sql text also associated with the lock.

Now I am not able to join the v$sqltext, for some reason, it gives shitty data when i try to do that. Can any one please, please help me out? I need this like asap.

I am attaching the script, called lock.sql

Thanks and regards,

raja

Get 250 color business cards for FREE!
http://businesscards.lycos.com/vp/fastpath/

select   OS_USER_NAME as OSUSER,s.serial# as "SERIAL NO.",     
         PROCESS as "PID",                                     
         ORACLE_USERNAME as "USERNAME" ,                       
         l.SID as "SID",                                       
         DECODE(l.TYPE                                         
   -- Long locks                                               


         , 'TM', 'dml/data enq (TM)'                           

         , 'TX', 'transac enq (TX) '                           

         , 'UL', 'pls usr lock (UL)'                           

   -- Short locks                                              


         , 'BL', 'buf hash tbl (BL)'                           

         , 'CF', 'control file (CF)'                           

         , 'CI', 'cross inst f (CI)'                           

         , 'CU', 'cursor bind (CU) '                           

         , 'DF', 'data file (CF)   '                           

         , 'DL', 'direct load (DL) '                           

         , 'DM', 'mount/strtup (DM)'                           

         , 'DR', 'reco lock (DR)   '                           

         , 'DX', 'distrib tran (DX)'                           

         , 'FI', 'sga opn file (FI)'                           

         , 'FS', 'file set (FS)    '                           

         , 'IN', 'instance num (IN)'                           

         , 'IR', 'instce recvr (IR)'                           

         , 'IS', 'get state (IS)   '                

         , 'IV', 'libcache inv (IV)'                

         , 'JQ', 'job queue (JQ)   '                

         , 'KK', 'log sw kick (KK) '                

         , 'LS', 'log switch (LS)  '                

         , 'MM', 'mount def (MM)   '                

         , 'MR', 'media recvry (MR)'                

         , 'PF', 'pwfile enq (PF)  '                

         , 'PR', 'process strt (PR)'                

         , 'RW', 'row wait (RW)    '                

         , 'RT', 'redo thread (RT) '                

         , 'SC', 'scn enq (SC)     '                

         , 'SM', 'smon lock (SM)   '                

         , 'SN', 'seqno instce (SN)'                

         , 'SQ', 'seqno enq (SQ)   '                

         , 'ST', 'space transc (ST)'                

         , 'SV', 'seqno value (SV) '                

         , 'TA', 'generic enq (TA) '                

         , 'TD', 'dll enq (TD)     '                

         , 'TE', 'extend seg (TE)  '                

         , 'TS', 'temp segment (TS)'                

         , 'TT', 'temp table (TT)  '                                        

         , 'UN', 'user name (UN)   '                                        

         , 'WL', 'write redo (WL)  '                                        

         , 'TYPE = ' || l.TYPE) AS type ,                                       

         DECODE(l.lmode, 0, 'none', 1, 'null', 2, 'RS', 3, 'RX', 4, 'S', 5, 'SRX', 6,  
                'Exclusive', 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,     
         decode(BLOCK,                                      
            0, 'Not Blocking',                                       
            1, 'Blocking',                                       
            2, 'Global', block) as "DETAIL",                                      
         OWNER,                                      
         OBJECT_NAME
from     sys.v_$locked_object lo, dba_objects do, sys.v_$lock l,          
         (SELECT a.sid, a.serial#                                         
          FROM v$session a, v$bgprocess b                                 
          WHERE a.paddr = b.paddr(+)) s                                   
where    lo.OBJECT_ID = do.OBJECT_ID                                      
AND      l.SID = lo.SESSION_ID                                            
and s.sid = l.sid                                                         
/