How can I find the sql cause lock
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: How can I find the sql cause lock

  1. #1
    Join Date
    May 2002
    Posts
    34
    I want to find the sql cause lock(uncommit or rollback) sql
    Can you give me the scripts ? Thanks !!! Xy

  2. #2
    Join Date
    May 2002
    Posts
    34
    I find the sql myself

    select a.name, b.xacts, c.sid, c.serial#, c.username, d.sql_text
    from v$rollname a, v$rollstat b, v$session c,
    v$sqltext d,v$transaction e
    where a.usn = b.usn
    and b.usn = e.xidusn
    and c.taddr = e.addr
    and c.sql_address = d.address
    and c.sql_hash_value = d.hash_value
    order by a.name, c.sid, d.piece ;

  3. #3
    Join Date
    May 2002
    Posts
    34

    The shortcoming of this sql!

    If the session cause lock issue another sql
    such as "select count(*) from test "
    after "delete from test" ,you can only see the sql "select count(*) from test ", Can you deal it ?

  4. #4
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938

    Re: The shortcoming of this sql!

    Try this one too:

    Code:
    column Kill heading 'Kill String' format a13 
    column res heading 'Resource Type' format 999 
    column id1 format 9999990 
    column id2 format 9999990 
    column lmode heading 'Lock Held' format a20 
    column request heading 'Lock Requested' format a20 
    column serial# format 99999 
    column username  format a10  heading "Username" 
    column terminal heading Term format a10 
    column tab format a35 heading "Table Name" 
    column owner format a9 
    column Address format a18 
    select nvl(S.USERNAME,'Internal') username, 
     nvl(S.TERMINAL,'None') terminal, 
     L.SID||','||S.SERIAL# Kill, 
     U1.NAME||'.'||substr(T1.NAME,1,20) tab, 
     decode(L.LMODE,1,'No Lock', 
      2,'Row Share', 
      3,'Row Exclusive', 
      4,'Share', 
      5,'Share Row Exclusive', 
      6,'Exclusive',null) lmode, 
     decode(L.REQUEST,1,'No Lock', 
      2,'Row Share', 
      3,'Row Exclusive', 
      4,'Share', 
      5,'Share Row Exclusive', 
      6,'Exclusive',null) request 
    from V$LOCK L,  
     V$SESSION S, 
     SYS.USER$ U1, 
     SYS.OBJ$ T1 
    where L.SID = S.SID  
    and T1.OBJ# = decode(L.ID2,0,L.ID1,L.ID2)  
    and U1.USER# = T1.OWNER# 
    and S.TYPE != 'BACKGROUND' 
    order by 1,2,5 
    /
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width