Purdue University - Department of Computer Science - Oracle: ORA-00054

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

  1. What is this error?

    Oracle will maintain data integrity, when you have changes made to a table in one session that have not been committed, you are not permitted to make further changes from another session until the status of those in the first session are resolved.

    • An example of this would be, you insert ten rows of type 'phone' and then leave this in an uncommitted state.  If in the second session all rows of type 'phone' are deleted..  the exepcted outcome of these two statments can not be derived and the second will be prohibited until the first is resolved
  1. What are the causes of this error
    1. You have multiple active sqlplus connections open to the database
    2. You have been disconnected from the Oracle server and have opened a new sqlplus connection
  2. How can I correct or work around this condition
    1. If you have multiple sqlplus windows open issue 'commit' or 'rollback' in one or both of them, start your work again
    2. If you have been disconnected with uncommitted changes
      1. Your session will eventually time out, when this happens changes will roll back and you will be able to alter the tables again
      2. It is only the specific tables with uncommitted changes that are locked, you can change the table name and continue to work on your project
      3. There may be a unix process of 'sqlplus' still running after your connection was interrupted, if you kill this process it may speed your recovery
  3. How can I avoid this condition
    1. commit:  commit often and regularly if your work allows it, or rollback often and regularly if it makes more sense
    2. The unix commands: screen, or tmux may be used to provide a more stable platform to work on, they allow your session to survive an interruption of network connectivity and resume it once you have reconnected.
    3. For those users programming in perl, php, python, C, and etc...  An error resulting in the halting of your program will also cause this condition. You must trap errors and handle proper shutdown of the connections and cursors you have opened to avoid this problem.
Last Updated: Apr 21, 2017 4:18 PM

Department of Computer Science, 305 N. University Street, West Lafayette, IN 47907

Phone:(765) 494-6010 • Fax: (765) 494-0739

Copyright © 2016 Purdue University | An equal access/equal opportunity university | Copyright Complaints

Trouble with this page? Disability-related accessibility issue? Please contact the College of Science.