timeout occurred while waiting to lock object
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: timeout occurred while waiting to lock object

Hybrid View

  1. #1
    Join Date
    Sep 2000
    Location
    Chicago, IL
    Posts
    316

    timeout occurred while waiting to lock object

    We have a 4-node RACed database (9.2.0.2.0). Last night many of our nightly batch jobs aborted with this error:

    ORA-04021: timeout occurred while waiting to lock object SYS.DBMS_SYS_SQL

    They were all trying to do a truncate table, via a package that we have created to control truncate on specific schemas (this pkg has been working fine for months-no problems). This error was received on all the instances. I did take a look at the global view of v$access, and saw 1 or 2 other processes using the package, but nothing was different from the previous nights; same schedule. I did not see anything in dba_blockers. Also, I was able to use the package to do a truncate, as a test, from a sql*plus session !

    Anything else I should have done, looked into ?

    We ended up recyclying all the instances, and the problem was gone ! I hate these kind of issues - nothing to look into, no traces, no dump.

    I would appreciate any input-thanks.

  2. #2
    Join Date
    Mar 2008
    Posts
    1
    for those standard packages, sometimes it worked by reset the SYS password from the server.

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Next time login as SYS and try...
    Code:
    select  decode(lob.kglobtyp, 
            0, 'NEXT OBJECT ', 
            1, 'INDEX ', 
            2, 'TABLE ', 
            3, 'CLUSTER ', 
            4, 'VIEW ', 
            5, 'SYNONYM ', 
            6, 'SEQUENCE ', 
            7, 'PROCEDURE ', 
            8, 'FUNCTION ', 
            9, 'PACKAGE ', 
            11, 'PACKAGE BODY ', 
            12, 'TRIGGER ', 
            13, 'TYPE ', 
            14, 'TYPE BODY ', 
            19, 'TABLE PARTITION ', 
            20, 'INDEX PARTITION ', 
            21, 'LOB ', 
            22, 'LIBRARY ', 
            23, 'DIRECTORY ', 
            24, 'QUEUE ', 
            28, 'JAVA SOURCE ', 
            29, 'JAVA CLASS ', 
            30, 'JAVA RESOURCE ', 
            32, 'INDEXTYPE ', 
            33, 'OPERATOR ', 
            34, 'TABLE SUBPARTITION ', 
            35, 'INDEX SUBPARTITION ', 
            40, 'LOB PARTITION ', 
            41, 'LOB SUBPARTITION ', 
            42, 'MATERIALIZED VIEW ', 
            43, 'DIMENSION ', 
            44, 'CONTEXT ', 
            46, 'RULE SET ', 
            47, 'RESOURCE PLAN ', 
            48, 'CONSUMER GROUP ', 
            51, 'SUBSCRIPTION ', 
            52, 'LOCATION ', 
            55, 'XML SCHEMA ', 
            56, 'JAVA DATA ', 
            57, 'SECURITY PROFILE ', 
            59, 'RULE ', 
            62, 'EVALUATION CONTEXT ', 
            'UNDEFINED ') object_type, 
            lob.kglnaobj object_name, 
            pn.kglpnmod lock_mode_held, 
            pn.kglpnreq lock_mode_requested, 
            ses.sid, 
            ses.serial#, 
            ses.username 
    from    sys.v$session_wait vsw, 
            sys.x$kglob lob, 
            sys.x$kglpn pn, 
            sys.v$session ses 
    where   vsw.event = 'library cache lock ' 
    and     vsw.p1raw = lob.kglhdadr 
    and     lob.kglhdadr = pn.kglpnhdl 
    and     pn.kglpnmod != 0 
    and     pn.kglpnuse = ses.saddr 
    /
    Last edited by PAVB; 03-20-2008 at 04:00 AM.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  4. #4
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    he would ofcourse have had library cache lock, however i dont see any remedy oracle gives in such cases. It just comes out with timeout after waiting for hundereds of seconds. re-run would work. i guess alter system flush shared pool may work too.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    I would suggest an active strategy to deal with the issue instead of a passive one.

    1- You have to determine who's holding the library cache lock, that's why I provided the script.
    2- You have to determine the status of the holding session and understand why the lock is held; depending of your findings you have to decide either to wait or to terminate the offending session.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  6. #6
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Thats right, active strategy is better than passive. However here in this case,
    PS the package would/can/should (be able to) be accessed by N number of session simultaneously & all will/can hold a shared lock on that object (momentarily).. if its not than i see oracle is not handling pinned objects properly. terminating a session would/may solve, but point is why any (or rather how any) user session will have exclusive locks on sys owner objects?

    -Abhay
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  7. #7
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    You forgot the "compile" scenario.
    If somebody is compiling the package and for some reason it hangs or keeps waiting because somebody is using it a new attempt to use the package would find it locked.
    Not saying that's what happened, troubleshoot -perhaps using my script?- is needed for a fair determination of the root cause of the issue.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    i did consider that, thats why the Q "why any user session will have exclusive locks on sys owned objects".. why anyone would re-complie a sys package unless there is a upgrade, re-run catalog (even this why anyone would like to do?).. recompile of packages will be done only when its invalid, but why would such a package go invalid?..

    in a prd environment, such things on SYS schema is hardly done.

    - Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

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