Oracle Error 0054
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up What is the reason for ORA-00054 error? up vote 0 down vote favorite From Oracle's documentation:- ORA-00054 resource busy and acquire with NOWAIT specified Cause: Resource interested is busy. Action: Retry if necessary. In our code we issue a SELECT FOR UPDATE NOWAIT command to lock the row we are about to update. Right now the logic is if it returns SQL error 54 then it is assumed that another user is trying to update that same record. Is this logic valid? From Oracle's documentation it looks more like if the DB is overwhelmed then this might also cause this error to be thrown. What are the possible reasons for this error, when we are only using the above SQL command? oracle oracle11g ora-00054 share|improve this question edited May 1 '12 at 18:58 Ben 33k216499 asked Apr 27 '12 at 12:43 AppleGrew 3,224114487 1 Does the error return immediately, or as a timeout? –skaffman Apr 27 '12 at 12:45 I cannot debug or put sops there, so I am not sure if it returns immediately. The code is running in a standalone server. –AppleGrew Apr 27 '12 at 12:48 add a comment| 1 Answer 1 active oldest votes up vote 2 down vote accepted The SELECT ... FOR UPDATE attempts to acquire an RS (Row Share) lock on the table and an X (eXclusive) lock on the row. If another session has an exclusive lock on the table (eg creating an index) or an exclusive lock
SQL TuningSecurityOracle UNIXOracle LinuxMonitoringRemote supportRemote plansRemote servicesApplication Server ApplicationsOracle FormsOracle PortalApp UpgradesSQL ServerOracle ConceptsSoftware SupportRemote Support Development Implementation Consulting StaffConsulting PricesHelp Wanted! Oracle PostersOracle Books Oracle Scripts Ion Excel-DB Don Burleson Blog
Locks and ORA-00054 error Oracle Database Tips by Burleson Consulting Question: I'm trying to alter a table, and I get an ORA-00054, from what appears to be http://stackoverflow.com/questions/10350821/what-is-the-reason-for-ora-00054-error a locking problem: alter table mytab add newcol char * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified How do I stop the ORA-00054 error? Answer: When I get an ORA-00054, it's usually impossible to do the DDL unless I spend hours inconveniencing end-users by nuking their sessions. http://www.dba-oracle.com/t_ora_00054_locks.htm For related content, see here, details on the Oracle deadly embrace. Unfortunately, this is often unavoidable in a 24x7 database, especially when you are restructuring a table online with the dbms_redefinition utility. Fixing the ORA-00054 error You need to schedule the 'alter table' for when there is low activity and notify the end-users about the scheduled maintenance. Before 11g, you can mark the tablespace read-only for the duration of the alter table: alter tablespace tab_ts read only In 11g, you can mark the table as read-only to prevent updates during an alter table: alter table mytab read only You can verify that the table is read-only with this command: select table_name, read_onlyfrom dba_tables where owner = ?myowner? and table_name = 'MYTAB'; As an alternative to making the table read-only, you can create a temporary trigger to prevent updates: create or replace trigger tabl_read_only before insert or update or delete on mytab begin raiseCommunityOracle User Group CommunityTopliners CommunityOTN Speaker BureauJava CommunityError: You don't have JavaScript enabled. This tool uses JavaScript and much of it will not work correctly without it enabled. https://community.oracle.com/thread/2552670 Please turn JavaScript back on and reload this page. Please https://www.tekstream.com/resources/ora-00054-resource-busy-or-timeout-expired/ enter a title. You can not post a blank message. Please type your message and try again. More discussions in General Database Discussions All PlacesDatabaseGeneral Database Discussions This discussion is archived 4 Replies Latest reply on Jun 20, 2013 1:14 oracle error PM by 985871 ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired 985871 Jun 20, 2013 12:58 PM Hi All , I am using oracle 11.2.0.3 with 3 node rac with ODI . I have the below table which i am not able to drop and giving the oracle error 0054 error . SQL> drop table I$_CDS_FAULT_DEV1;drop table I$_CDS_FAULT_DEV1 *ERROR at line 1:ORA-00054: resource busy and acquire with NOWAIT specified or timeout expiredSQL> show parameter ddl_lockNAME TYPE VALUE------------------------------------ ----------- ------------------------------ddl_lock_timeout integer 300i changed to 100 but still the same error also i have checked and there are no locks . Can you please provide me a way to resolve this issue . Regards , DBApps 54578Views Tags: none (add) 00054Content tagged with 00054, busyContent tagged with busy, resourceContent tagged with resource This content has been marked as final. Show 4 replies 1. Re: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired DK2010 Jun 20, 2013 1:01 PM (in response to 985871) Hi,It means some session accessing that table. you can Query from v$accessFind that sid and kill that sid and drop the tableHTH Like Show 0 Likes(0) Actions 2. Re: ORA-00054: resource busy and acquire with NOWAIT
Digital Records Management Enterprise Content Management Strategy Digital Asset Management Oracle Imaging & Process Management Web Content Management Oracle WebCenter Portal Enterprise Portal Support Enterprise Portal Strategy Enterprise Portal Upgrade Oracle WebCenter Sites Sourcing Staffing & Recruiting Recruiting Managed Services Candidate Registration Technical Focus Client Opportunities Support Solutions Training Legacy to Oracle WebCenter Oracle Documents Cloud Service Next Generation AP Automation & Dynamic Discounting Oracle WebCenter Contract Lifecycle Management (CLM) Search ORA-00054: resource busy and acquire with NOWAIT specified or timeout expiredYou are here: Home / Resources / ORA-00054: resource busy and acquire with NOWAIT specified or timeout ... ORA-00054 Error Message Error ORA-00054 is a commonly seen error by Oracle users and occurs when a user tries to execute a LOCK TABLE or SELECT FOR UPDATE command with the NOWAIT keyword when the resource is unavailable. DDL or DML operations are being run concurrently without proper commits. In most cases, Error ORA-00054? occurs from a session. Any session that has referenced the table and any structural change attempt, such as adding a column, requires an “exclusive" lock. There are several options for tables or views to see locks and specific information about the locks: DBA_BLOCKERS: Shows non-waiting sessions holding locks being waited on DBA_DDL_LOCKS: Shows all DDL locks held or being requested DBA_DML_LOCKS: Shows all DML locks held or being requested DBA_LOCK_INTERNAL: Displays 1 row for every lock/latch held or being requested with the username of who is holding lock DBA_LOCKS: Shows all locks/latches held or being requested DBA_WAITERS: Shows all sessions waiting on but not holding waited for locks The first step the use