ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
ORA-00054 indicates that a resource is being locked by another ongoing (uncommitted) transaction. It usually arises when you try to acquire a lock on a whole table in order to execute a DDL (create index, alter table, etc.).
Usual recommendations are:
- to wait until the table is freed
- retry the operation during off-peak hours
- kill the bloody session that locks the table
- put the table in read-only mode
- put the whole database in the maintenance mode
- and so on
Unfortunately, some tables are never freed. Multiple sessions of automated data stream working 24/7 do not have off-peak hours. The table may never be free for an EXLUSIVE LOCK required for a DDL. Furthermore, it’s not an option to kill a session as it may disrupt the data flow and cause a Service Level Agreement violation. Database maintenance window is a solution, but next scheduled window may be far away. So what if we need this DDL right now.
Oracle Database 11g has a convenient workaround.
You may specify DDL_LOCK_TIMEOUT. This parameter will cause DDL statement to wait specified number of seconds for the table to become available. All concurrent sessions are enqueued as well. Therefore, this options works even if there are multiple sessions working with the table. You can specify DDL_LOCK_TIMEOUT for your session only by issuing:
ALTER SESSION SET DDL_LOCK_TIMEOUT=300;
Read more: http://oracle-base.com/articles/11g/ddl-lock-timeout-11gr1.php
Oracle Database 10g doesn’t have this option
However, there is a workaround. It is a little awkward and I didn’t see it anywhere else, but if you need the job done, it will do the trick. This has been successfully tested on Oracle Database 10.2.0.4. The idea behind the proposed solution is to:
- First, make all sessions that execute statements on this table work in serial (consecutively).
- Second, create a management session that will work in serial mode along with sessions in the first step.
- Third, execute your DDL.
- Finally, revert to normal functioning.
Basically, this mimics the 11g DDL_LOCK_TIMEOUT or DATABASE QUIESCE feature. How is it achievable? Using Resource Manager ACTIVE SESSIONS limit. If you are familiar RESOURCE MANAGER, then you probably already deduced the solution. Like the DDL_LOCK_TIMEOUT this method will cause a slowdown for sessions interacting with your table. However, in comparison to the QUIESCE method, you may limit the impact to an absolute minimum. QUIESCE, on the other hand, will pause every session across the whole database and sometimes takes forever to execute.
You may probably have seen proposition to use LOCK TABLE function. This is wrong and will not work. Even if it seems to work – it doesn’t, only by pure luck. When performing any DDL operation, Oracle forces a COMMIT before the DDL is executed. So the lock set by LOCK TABLE is lost and then should be reacquired for the following DDL. If you have outstanding sessions that are waiting to place any type of lock on this table, the DDL will fail to execute with the same ORA-00054: resource busy error. Do not use LOCK TABLE.
Step-By-Step How-To for 10g
For this example, suppose you have an ERP schema with TRANSACTION table. There are always at least two overlapping sessions that insert data into TRANSACTION table (test case with actual DML/DDL will be posted shortly).
First step: create a resource consumer group (ex: SERIAL_GROUP) and simple resource plan (ex: SERIAL_PLAN) with a limit of one Active Session for SERIAL_GROUP. It is important, that the group uses RUN-TO-COMPLETION scheduling. Grant SERIAL_GROUP to public.
Create consumer group:
begin sys.dbms_resource_manager.clear_pending_area(); sys.dbms_resource_manager.create_pending_area(); sys.dbms_resource_manager.create_consumer_group ( consumer_group => 'SERIAL_GROUP' ,comment => 'Enforces serial execution.' ,cpu_mth => 'RUN-TO-COMPLETION'); sys.dbms_resource_manager.submit_pending_area(); end; / begin sys.dbms_resource_manager.clear_pending_area(); sys.dbms_resource_manager.create_pending_area(); sys.dbms_resource_manager_privs.grant_switch_consumer_group ( grantee_name => 'PUBLIC' ,consumer_group => 'SERIAL_GROUP' ,grant_option => TRUE); sys.dbms_resource_manager.submit_pending_area(); end; /
Create Resource Plan:
begin sys.dbms_resource_manager.clear_pending_area(); sys.dbms_resource_manager.create_pending_area(); sys.dbms_resource_manager.create_plan ( plan => 'SERIAL_PLAN' ,cpu_mth => 'EMPHASIS' ,active_sess_pool_mth => 'ACTIVE_SESS_POOL_ABSOLUTE' ,parallel_degree_limit_mth => 'PARALLEL_DEGREE_LIMIT_ABSOLUTE' ,queueing_mth => 'FIFO_TIMEOUT' ,comment => 'Maintenance plan.'); sys.dbms_resource_manager.create_plan_directive ( plan => 'SERIAL_PLAN' ,group_or_subplan => 'OTHER_GROUPS' ,switch_estimate => FALSE ,comment => 'Mandatory group' ); sys.dbms_resource_manager.create_plan_directive ( plan => 'SERIAL_PLAN' ,group_or_subplan => 'SERIAL_GROUP' ,active_sess_pool_p1 => 1 ,switch_estimate => FALSE ,comment => 'Maintenance group' ); sys.dbms_resource_manager.submit_pending_area(); end; /
Second step: assign sessions that are blocking our table to SERIAL_GROUP. This may be the trickiest part. The strategy to put sessions in the required group will vary depending on how the application software handles database connections and the performance impact one may tolerate. For the sake of this demonstration let’s use Client Program. We’ll pretend that our clients use SQLPlus. The mapping below will put every sqlplus.exe session into SERIAL_GROUP.
BEGIN dbms_resource_manager.clear_pending_area(); dbms_resource_manager.create_pending_area(); dbms_resource_manager.set_consumer_group_mapping( dbms_resource_manager.client_program, 'sqlplus.exe', 'SERIAL_GROUP' ); dbms_resource_manager.submit_pending_area(); END;
CAUTION: Use another session that will not be mapped to the SERIAL_GROUP to setup and control the environment. You risk to be locked-out of your database.
Optionally, make sure that your mapping is of a higher priority. We’ll put client program to be priority 2 (1 – for explicitly assigned groups)
BEGIN dbms_resource_manager.clear_pending_area(); dbms_resource_manager.create_pending_area(); dbms_resource_manager.set_consumer_group_mapping_pri( EXPLICIT => 1, CLIENT_PROGRAM => 2, SERVICE_MODULE_ACTION => 3, SERVICE_MODULE => 4, MODULE_NAME_ACTION => 5, MODULE_NAME => 6, SERVICE_NAME => 7, ORACLE_USER => 8, CLIENT_OS_USER => 9, CLIENT_MACHINE => 10 ); dbms_resource_manager.submit_pending_area(); END;
You may probably be required to use a combination of mapping to put users’ sessions and yours into serial group. Using finer grained mapping such as modules and/or actions may greatly reduce any performance hit. Check the documentation for more: https://docs.oracle.com/cd/B19306_01/server.102/b14231/dbrm.htm
Third step: Now when you are ready, connect to the database in such a way that your new session will be mapped to SERIAL_GROUP. In our case, we’ll use SQLPlus.
In the session that is NOT mapped to SERIAL_GROUP activate the SERIAL_PLAN.
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'SERIAL_PLAN';
In the SERIAL_GROUP session, execute your DDL. If there is pending DDL LOCKS it will hang until the object is freed.
When done or just tired of waiting, deactivate SERIAL PLAN by issuing:
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = '';
That’s it. If you have any questions, write them in comments below!