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 user should take in fixing this error is to wait a few minutes, then try the command again. This simple step may solve the problem. If this does not work, identify the database session locking the object and kill this session.

To identify the session, write the following SQL statement:

select a.sid, a.serial#

from v$session a, v$locked_object b, dba_objects c

where b.object_id = c.object_id

and a.sid = b.session_id

and OBJECT_NAME=’EMP’;

To kill the session and unlock the tables, write the following statement:

SQL>alter system kill session ‘sid, serial#’;

Another option to fix this error in Oracle 11g is to make your table read-only to prevent DML.

Take the following SQL statement example:

SQL> alter table emp add (cust_id varchar2(3));

To alter the session table, execute the following:

SQL>alter table emp read only;

SQL> alter table emp add (cust_id varchar2(3));

Another solution in Oracle 11g is to create a timeout to set how long you want DDL to wait for the object to become available. To set a timeout, write the following statement:

SQL> alter session set ddl_lock_timeout = time_to_wait;

SQL> alter table emp add (cust_id varchar2(3));

To avoid seeing Error ORA-00054? in the future, practice the following tips:

Execute DDL during off-peak hours when the database is idle, such as late at night.

Execute DDL during a maintenance window when all the end-users are locked out.

Identify and kill the session that is preventing the exclusive lock.