Locks and ORA-00054 error Oracle Database Tips by Donald Burleson

 

Question:  I'm trying to alter a table, and I get an ORA-00054, from what appears to be 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. 

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_only
from
   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

raise_application_error (-999999, 'Table mytab is undergoing maintenance.  Try again later.');

end; /

Details on locks and the ORA-00054 error

The Oracle docs are vague on the ORA-00054 error, only suggesting that you re-try the transaction:  

ORA-00054: resource busy and acquire with NOWAIT specified

Cause: Resource interested is busy.

Action: Retry if necessary.

As you see, any session that has referenced the table will require an "exclusive" lock, and always when you attempt a structural change such as adding or deleting columns.

 

You have choices for avoiding the ORA-00054 error:

• Re-run the change late at night when the database is idle.
   

• Do all DDL during a maintenance window with all end-users locked-out.
   

• Kill the sessions that are preventing the exclusive lock. 

• 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 or latch held or being requested with the username of who is holding the lock 
• DBA_LOCKS  - Shows all locks or latches held or being requested
• DBA_WAITERS  - Shows all sessions waiting on, but not holding waited for locks

 

You can also query v$access and v$locked_object to see specific locks: 

 

select s.sid, s.serial#, p.spid
from
   v$session s,
   v$process p
where
   s.paddr = p.addr
and
   s.sid in (select SESSION_ID from v$locked_object);

 

If you don't want to write your own script, you can use the Oracle script collection for this, highly recommended because of the complexity of the locking structures.

[http://dba-oracle.com/include_tuning_book3.htm]