Mir Sayeed Hassan – Oracle Blog

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

The above error accour while creation of the table in database.

Create table statement

create table MIR.RELATION
(
ID VARCHAR2(255 char) not null primary key,
DESTINATION_ID VARCHAR2(255 char) not null,
SOURCE_ID VARCHAR2(255 char) not null,
);

Error report – ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired 00054. 00000 – “resource busy and acquire with NOWAIT specified or timeout expired” *Cause: Interested resource is busy. *Action: Retry if necessary or increase timeout.

Solution to overcome this issue.

This cause due to the exclusive lock on a table., it can be DDL/DML transaction fail and acquire the lock for table., therefore the transaction will failed and get the above resource busy error.

Solution 1 – You can increase the below parameter as shown below, therefore the DDL will wait in the lock queue for the resource to be available for execution.

alter session set ddl_lock_timeout=300;

Solution 2 – There is another solution by killing the active session immediately.

Check the object locked by query

SQL> 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);

Kill the object locked session

alter system kill session '1630,57281' immediate;

create table.

create table MIR.RELATION
(
ID VARCHAR2(255 char) not null primary key,
DESTINATION_ID VARCHAR2(255 char) not null,
SOURCE_ID VARCHAR2(255 char) not null,
);
Table MIR.RELATION created.

=====Hence tested & verified in our test env====