Session Lock- V$lock, ORA 0054
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Session Lock- V$lock, ORA 0054

  1. #1
    Join Date
    May 2000
    Location
    fremont ca
    Posts
    182
    Our application hangs all the time. many time it gives error
    ORA 0054 resource busy.
    How can I find which session has lock which table via which SQL. and what type lock it is ? I try to check in V$lock, but I do not understand all the field in V$lock. Does any body knows how to detect lock ?
    in v$Lock which is Idb1, idb2, lmode etc..
    Where will I get information of data dictnery tables.
    How can I resolve lock ?????


  2. #2
    Join Date
    Nov 2001
    Posts
    11
    The following query will identify users that are currently being locked in the system.

    select b.username, b.serial#, d.idl1, a.sql_text
    from v$session b, v$lock d, v$sqltext a
    where b.lockwait = d.kaddr
    and a.address = b.sql_address
    and a.hash_value = b.sql_hash_value;

    The next query will identify users in the system that are causing the problems of locking.

    select a.serial#, a.sid, a.username, b.idl1, c.sql_text
    from v$session a, v$lock b, v$sqltext c
    where b.id1 in
    (select distinct e.id1
    from v$session d, v$lock e
    where d.lockwait = e.kaddr)
    and a.sid = b.sid
    and c.hash_value = a.sql_hash_value
    and b.request = 0;


    I hope this helps, if not let me know and I will see what else I have in my bag of tricks!
    Learning something new everyday
    Forgetting something useful every minute!

  3. #3
    Join Date
    May 2000
    Location
    fremont ca
    Posts
    182
    Will it capture lock occured by
    for update nowait ??

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    you have many select for update right?

  5. #5
    Join Date
    May 2000
    Location
    fremont ca
    Posts
    182
    Programmers has wriiten all
    select statment using for update nowait for all tables
    so we are not able to capture lock.
    many time we get resource busy ORA-0054.
    System gets hangs for hours and hours



  6. #6
    Join Date
    May 2000
    Location
    fremont ca
    Posts
    182
    Doing update all the time in OLTP enviorment.

  7. #7
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    You need to seriously look at your application code and review it with the developers. For most part, Oracle default locking mechanisms are enough and you do not need so many locks for update nowait.

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  8. #8
    Join Date
    Nov 2001
    Posts
    11
    seriously agree with Raminder
    Learning something new everyday
    Forgetting something useful every minute!

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