ORA-00054 resource busy and acquire with NOWAIT specified or timeout expired

Question

Why am I getting this database error when I update a table      ERROR at line 1    ORA-00054  resource busy and acquire with NOWAIT specified or timeout expired

User · Answer

In my case  I was quite sure it was one of my own sessions which was blocking  Therefore  it was safe to do the following    I found the offending session with   SELECT   FROM V SESSION WHERE OSUSER  my local username    The session was inactive  but it still held the lock somehow  Note  that you may need to use some other WHERE condition in your case  e g  try USERNAME or MACHINE fields   Killed the session using the ID and SERIAL  acquired above   alter system kill session   lt id gt    lt serial  gt      Edited by  thermz  If none of the previous open-session queries work try this one  This query can help you to avoid syntax errors while killing sessions    SELECT  ALTER SYSTEM KILL SESSION      SID       SERIAL       immediate   FROM V SESSION WHERE OSUSER  my local username on OS

User · Answer

There is a very easy work around for this problem    If you run a 10046 trace on your session  google this    too much to explain   You will see that before any DDL operation Oracle does the following   LOCK TABLE  TABLE NAME  NO WAIT  So if another session has an open transaction you get an error  So the fix is    drum roll please  Issue your own lock before the DDL and leave out the  NO WAIT     Special Note   if you are doing splitting dropping partitions oracle just locks the partition   -- so yo can just lock the partition subpartition    So    The following steps fix the problem    LOCK TABLE  TABLE NAME   -- you will  wait   developers call this hanging   until the session with the open transaction  commits  This is a queue  so there may be several sessions ahead of you  but you will NOT error out   Execute DDL  Your DDL will then run a lock with the NO WAIT  However  your session has aquired the lock  So you are good  DDL auto-commits  This frees the locks    DML statements will  wait  or as developers call it  hang  while the table is locked    I use this in code that runs from a job to drop partitions  It works fine  It is in a database that is constantly inserting at a rate of several hundred inserts second  No errors    if you are wondering  Doing this in 11g  I have done this in 10g before as well in the past

User · Answer

This error happens when the resource is busy  Check if you have any referential constraints in the query  Or even the tables that you have mentioned in the query may be busy  They might be engaged with some other job which will be definitely listed in the following query results   SELECT   FROM V SESSION WHERE STATUS    ACTIVE    Find the SID    SELECT   FROM V OPEN CURSOR WHERE SID   --the id

User · Answer

Your table is already locked by some query  For example  you may have executed  select for update  and have not yet committed rollbacked and fired another select query  Do a commit rollback before executing your query

User · Answer

Please Kill Oracle Session  Use below query to check active session info  SELECT     O OBJECT NAME      S SID      S SERIAL       P SPID      S PROGRAM      SQ SQL FULLTEXT      S LOGON TIME FROM     V LOCKED OBJECT L      DBA OBJECTS O      V SESSION S      V PROCESS P      V SQL SQ WHERE     L OBJECT ID   O OBJECT ID     AND L SESSION ID   S SID     AND S PADDR   P ADDR     AND S SQL ADDRESS   SQ ADDRESS    kill like  alter system kill session  SID SERIAL       For example  alter system kill session  13 36543     Reference http   abeytom blogspot com 2012 08 finding-and-fixing-ora-00054-resource html

User · Answer

This happens when a session other than the one used to alter a table is holding a lock likely because of a DML  update delete insert   If you are developing a new system  it is likely that you or someone in your team issues the update statement and you could kill the session without much consequence  Or you could commit from that session once you know who has the session open   If you have access to a SQL admin system use it to find the offending session  And perhaps kill it   You could use v session and v lock and others but I suggest you google how to find that session and then how to kill it   In a production system  it really depends  For oracle 10g and older  you could execute   LOCK TABLE mytable in exclusive mode  alter table mytable modify mycolumn varchar2 5     In a separate session but have the following ready in case it takes too long   alter system kill session         It depends on what system do you have  older systems are more likely to not commit every single time  That is a problem since there may be long standing locks  So your lock would prevent any new locks and wait for a lock that who knows when will be released  That is why you have the other statement ready  Or you could look for PLSQL scripts out there that do similar things automatically   In version 11g there is a new environment variable that sets a wait time  I think it likely does something similar to what I described  Mind you that locking issues don t go away   ALTER SYSTEM SET ddl lock timeout 20  alter table mytable modify mycolumn varchar2 5     Finally it may be best to wait until there are few users in the system to do this kind of maintenance

User · Answer

I managed to hit this error when simply creating a table   There was obviously no contention problem on a table that didn t yet exist   The CREATE TABLE statement contained a CONSTRAINT fk name FOREIGN KEY clause referencing a well-populated table   I had to    Remove the FOREIGN KEY clause from the CREATE TABLE statement Create an INDEX on the FK column Create the FK

User · Answer

Your problem looks like you are mixing DML  amp  DDL operations  See this URL which explains this issue   http   www orafaq com forum t 54714 2

User · Answer

x000D   x000D  select x000D     c owner  x000D     c object name  x000D     c object type  x000D     b sid  x000D     b serial   x000D     b status  x000D     b osuser  x000D     b machine x000D  from x000D     v locked object a  x000D     v session b  x000D     dba objects c x000D  where x000D     b sid   a session id x000D  and x000D     a object id   c object id  x000D      x000D     ALTER SYSTEM KILL SESSION  sid serial    x000D   x000D   x000D

User · Answer

Just check for process holding the session and Kill it  Its back to normal   Below SQL will find your process  SELECT s inst id     s sid     s serial      p spid     s username     s program FROM   gv session s    JOIN gv process p ON p addr   s paddr AND p inst id   s inst id    Then kill it   ALTER SYSTEM KILL SESSION  sid serial     OR   some example I found online seems to need the instance id as well alter system kill session  130 620  1

User · Answer

I also face the similar Issue  Nothing programmer has to do to resolve this error  I informed to my oracle DBA team  They kill the session and worked like a charm

User · Answer

I had this error happen when I had 2 scripts I was running   I had    A SQL Plus session connected directly using a schema user account  account  1  Another SQL Plus session connected using a different schema user account  account  2   but connecting across a database link as the first account   I ran a table drop  then table creation as account  1  I ran a table update on account  2 s session   Did not commit changes  Re-ran table drop creation script as account  1   Got error on the drop table x command   I solved it by running COMMIT  in the SQL Plus session of account  2

User · Answer

Solution given by Shashi s link is the best    no needs to contact dba or someone else  make a backup  create table xxxx backup as select   from xxxx    delete all rows  delete from xxxx  commit    insert your backup   insert into xxxx  select   from xxxx backup   commit

User · Answer

from here ORA-00054  resource busy and acquire with NOWAIT specified  You can also look up the sql username machine port information and get to the actual process which holds the connection  SELECT O OBJECT NAME  S SID  S SERIAL   P SPID  S PROGRAM S USERNAME  S MACHINE S PORT   S LOGON TIME SQ SQL FULLTEXT  FROM V LOCKED OBJECT L  DBA OBJECTS O  V SESSION S   V PROCESS P  V SQL SQ  WHERE L OBJECT ID   O OBJECT ID  AND L SESSION ID   S SID AND S PADDR   P ADDR  AND S SQL ADDRESS   SQ ADDRESS

[oracle] ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Examples related to oracle

Examples related to ora-00054