While working with Oracle databases, you might encounter:<\/p>\n\n\n\n
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired\n<\/code><\/code><\/pre>\n\n\n\nIf you\u2019re wondering:<\/p>\n\n\n\n
\n- Why does this error occur?<\/strong><\/li>\n\n\n\n
- What action should I take to fix it?<\/strong><\/li>\n\n\n\n
- How do I avoid it in the future?<\/strong><\/li>\n<\/ul>\n\n\n\n
This post will clarify these questions simply so you can handle this confidently in your DBA workflow.<\/p>\n\n\n\n
What is ORA-00054?<\/h3>\n\n\n\n
This error indicates that:<\/p>\n\n\n\n
\n- You tried to acquire a lock on a table or row<\/strong>,<\/li>\n\n\n\n
- NOWAIT or a timeout was specified<\/strong>,<\/li>\n\n\n\n
- But the resource is already locked by another session<\/strong>, so Oracle cannot acquire the lock immediately.<\/li>\n<\/ul>\n\n\n\n
This typically happens when you:<\/p>\n\n\n\n
\n- Run
ALTER TABLE<\/code> or DROP TABLE<\/code> on a table that is currently in use.<\/li>\n\n\n\n- Execute DDL statements on objects locked by active transactions.<\/li>\n\n\n\n
- Attempt DML with
SELECT FOR UPDATE NOWAIT<\/code> while another session is holding the lock.<\/li>\n<\/ul>\n\n\n\nWhy does it occur?<\/h3>\n\n\n\n
Oracle uses locks to maintain data consistency and concurrency control<\/strong>. If you request a lock with NOWAIT<\/code>, Oracle will not wait for the resource to become available and throws ORA-00054<\/strong> if it is already locked.<\/p>\n\n\n\nFor example:<\/p>\n\n\n\n
ALTER TABLE employees ADD (new_column NUMBER);\n<\/code><\/code><\/pre>\n\n\n\nwill fail with ORA-00054 if:<\/p>\n\n\n\n
\n- Another session is updating
employees<\/code>.<\/li>\n\n\n\n- A long-running transaction is holding a lock on
employees<\/code>.<\/li>\n<\/ul>\n\n\n\nWhat action is needed?<\/h3>\n\n\n\n1\ufe0f\u20e3 Identify the locking session<\/h4>\n\n\n\n
Run:<\/p>\n\n\n\n
SELECT \n l.session_id, \n s.serial#, \n s.username, \n s.machine, \n s.program \nFROM \n v$locked_object l, dba_objects o, v$session s \nWHERE \n l.object_id = o.object_id \n AND l.session_id = s.sid \n AND o.object_name = 'EMPLOYEES';<\/code><\/code><\/pre>\n\n\n\nReplace 'EMPLOYEES'<\/code> with your table name.<\/p>\n\n\n\nThis will show who is holding the lock<\/strong> on your object.<\/p>\n\n\n\n2\ufe0f\u20e3 Wait or Kill the session (with caution)<\/h4>\n\n\n\n
If the locking session is running a valid transaction:<\/p>\n\n\n\n
\n- Wait for it to complete<\/strong> before retrying your operation.<\/li>\n<\/ul>\n\n\n\n
If it is blocking and you need to proceed:<\/p>\n\n\n\n
\n- Check with your team and, if safe, you can kill the session:<\/li>\n<\/ul>\n\n\n\n
ALTER SYSTEM KILL SESSION 'sid,serial#';\n<\/code><\/code><\/pre>\n\n\n\n\u26a0\ufe0f Use this with extreme caution<\/strong> in production, ensuring you are not interrupting critical transactions.<\/p>\n\n\n\n3\ufe0f\u20e3 Retry without NOWAIT<\/h4>\n\n\n\n
If your statement used NOWAIT<\/code>, consider removing it to allow Oracle to wait until the lock is released:<\/p>\n\n\n\nSELECT * FROM employees FOR UPDATE;\n<\/code><\/code><\/pre>\n\n\n\ninstead of<\/p>\n\n\n\n
SELECT * FROM employees FOR UPDATE NOWAIT;\n<\/code><\/code><\/pre>\n\n\n\nOr handle it in application logic to retry after waiting.<\/p>\n\n\n\n
4\ufe0f\u20e3 Schedule DDL during maintenance windows<\/h4>\n\n\n\n
DDL operations should ideally be performed during low-activity windows<\/strong> to avoid conflicts with active transactions.<\/p>\n\n\n\nHow to Avoid ORA-00054 in the Future?<\/h3>\n\n\n\n\n- Schedule DDL changes during maintenance or periods of low user activity.<\/li>\n\n\n\n
- Use
DBMS_LOCK<\/code> or application logic to manage custom locking and avoid clashes.<\/li>\n\n\n\n- Monitor sessions with scripts\/tools before running DDL.<\/li>\n\n\n\n
- Train your team to communicate active locking operations before large changes.<\/li>\n<\/ul>\n\n\n\n
Summary<\/h3>\n\n\n\n\n- Why it occurs:<\/strong> Another session is holding a lock when you requested NOWAIT or a timeout expired.<\/li>\n\n\n\n
- What to do:<\/strong> Identify the blocking session, wait, kill if necessary, or retry without NOWAIT.<\/li>\n\n\n\n
- Prevention:<\/strong> Schedule DDL carefully and monitor active sessions.<\/li>\n<\/ul>\n\n\n\n
By understanding ORA-00054<\/strong> and how to handle it systematically, you will avoid unnecessary production disruptions<\/strong> and strengthen your Oracle administration confidence.<\/p>\n","protected":false},"excerpt":{"rendered":"While working with Oracle databases, you might encounter: If you\u2019re wondering: This post will clarify these questions simply so you can handle this confidently in your DBA workflow. What is ORA-00054? This error indicates that: This typically happens when you: Why does it occur? Oracle uses locks to maintain data consistency and concurrency control. If […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"googlesitekit_rrm_CAowu461DA:productID":"","footnotes":""},"categories":[1225],"tags":[],"class_list":["post-4657","post","type-post","status-publish","format-standard","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/4657","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/comments?post=4657"}],"version-history":[{"count":1,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/4657\/revisions"}],"predecessor-version":[{"id":4658,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/4657\/revisions\/4658"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=4657"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=4657"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/tags?post=4657"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}