ORA-00054 resource busy and acquire with NOWAIT specified

Question

I killed a script that was in the middle of updating a table  Now when I rerun the script I am getting      ORA-00054  resource busy and acquire   with NOWAIT specified   I presume the table is locked   How do I unlock the table   Thanks in advance

User · Answer

You ll have to wait  The session that was killed was in the middle of a transaction and updated lots of records  These records have to be rollbacked and some background process is taking care of that  In the meantime you cannot modify the records that were touched

User · Answer

When you killed the session  the session hangs around for a while in  KILLED  status while Oracle cleans up after it   If you absolutely must  you can kill the OS process as well  look up v process spid   which would release any locks it was holding on to   See this for more detailed info

User · Answer

Step 1   select object name  s sid  s serial   p spid  from v locked object l  dba objects o  v session s  v process p where l object id   o object id and l session id   s sid and s paddr   p addr    Step 2    alter system kill session  sid serial    -- sid  and  serial   get from step 1   More info  http   www oracle-base com articles misc killing-oracle-sessions php

User · Answer

Thanks for the info user  user712934   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

User · Answer

Depending on your situation  the table being locked may just be part of a normal operation  amp  you don t want to just kill the blocking transaction  What you want to do is have your statement wait for the other resource  Oracle 11g has DDL timeouts which can be set to deal with this      If you re dealing with 10g then you have to get more creative and write some PL SQL to handle the re-try    Look at Getting around ORA-00054 in Oracle 10g  This re-runs your statement when a resource busy exception occurs

[sql] ORA-00054: resource busy and acquire with NOWAIT specified

Examples related to sql

Examples related to oracle

Examples related to unix

Examples related to ora-00054