1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

ORA-04021: timeout occurred while waiting to lock object

Discussion in 'SQL PL/SQL' started by jagadekara, Jul 15, 2014.

  1. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi All,

    When i am executing one package specification in SQL Developer

    It is taking long time to execute and after that throws below error.

    Error report:
    ORA-04021: timeout occurred while waiting to lock object
    04021. 00000 - "timeout occurred while waiting to lock object %s%s%s%s%s"
    *Cause: While waiting to lock a library object, a timeout is occurred.
    *Action: Retry the operation later.

    What is this?
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.

    The package was in use. There are a couple of ways to find who is using a package at any given moment.

    You can join v$session to dba_ddl_locks or you can use v$acess but you can see who is executing the package. The code cannot be recompiled while the code is in use.

    See : select * from dba_ddl_locks where name = 'your package'
     
    jagadekara likes this.
  3. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Jagadekara,
    I suppose you have a "SELECT ... FOR UPDATE" instruction in the package you are executing.

    Add NOWAIT to the FOR UPDATE to prevent the waiting - it exits if there's a lock.
    And try to sort out the lock on the table in question.
     
    jagadekara likes this.
  4. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi Sergey,

    select * from dba_ddl_locks where name = 'JAI_PO_PROC_PKG';

    It returns 8 rows.

    Session_id Owner NAME TYPE MODE_HELD MODE_REQUESTED
    --------------------------------------------------------------------
    584 APPS JAI_PO_PROC_PKG Table/Procedure/Type Null None
    584 APPS JAI_PO_PROC_PKG Body Null None
    613 APPS JAI_PO_PROC_PKG Table/Procedure/Type Null None
    613 APPS JAI_PO_PROC_PKG Body Null None
    621 APPS JAI_PO_PROC_PKG Body Null None
    621 APPS JAI_PO_PROC_PKG Table/Procedure/Type Null None
    627 APPS JAI_PO_PROC_PKG Body Null None
    627 APPS JAI_PO_PROC_PKG Table/Procedure/Type Null None

    So What can I do now?
     
  5. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi Rajen,

    Actually It's a standard Package.
    In that I just want to test one thing. So I commented one variable in procedure. And When I execute it showing that error...
     
  6. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Possible options. Depends on what operation perform sessions :
    1) it is possible to wait for completion of operation of these sessions.
    2) it is possible to clarify that it for sessions and if they don't perform important operation, then it is possible to execute alter system kill session
     
  7. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Thanks Sergey,

    My DBA killed all locked sessions regarding my package.

    Now it is executed.

    Thanks A lot...