Steps to be follow to resolve a lock
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Steps to be follow to resolve a lock

Hybrid View

  1. #1
    Join Date
    Nov 2000
    Posts
    416

    Question

    Not very technical end-user come and complain that the record she requires is lock for a fair amount of time. Could you give me a systematic steps that you as a DBA expert will follow to find the root of the problem ( for example do you start to look at V$lock, V$session, OEM, I don't know what else ... )
    or if you have a all-inclusive script for similar case. I am looking for fastest and systematic way.
    An ounce of prevention is worth a pound of cure

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    The user could set a trace on the session as

    eg: for ORA-1591
    alter session set events '1591 trace name errorstack level 10';

    and analyze the trace that had been created at $ORACLE_BASE/admin/SID/udump

    To do this, the user should have the trace enabled.

    Hope this would help you.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Nov 2000
    Posts
    416
    Can you cover more different situations. Systematic approach.

    An ounce of prevention is worth a pound of cure

  4. #4
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Normally when you set the trace to the error and the trace level to 10 the max trace, it would spit out the entire stack

    One other query that you can use is

    select sid,
    sql_address,
    terminal,
    SUBSTR(OBJECT_NAME,1,20),
    b.sql_text
    FROM v$session,
    v$sqlarea b ,
    USER_OBJECTS
    WHERE process in ( select distinct process from v$locked_object )
    and sql_address = b.address AND USER_OBJECTS.OBJECT_ID IN ( SELECT OBJECT_ID FROM v$locked_object);


    to find the object causing the dead lock.


    This link under metalink would get your the scripts to monitor the locking

    http://metalink.oracle.com/metalink/...nical_Articles

    One other thing is that when deadlock occours oracle would create an ora-600 message and would create a trace file at udump for the corresponding sid. Check them for more detail.

    Sam
    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


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