Mir Sayeed Hassan – Oracle Blog

ORA-00054: resource busy and acquire with NOWAIT specified

Error occur while creating a table.

SQL> create table MIR.T_TRANSACTION(C_ID VARCHAR2(255 CHAR) not null primary key);

ORA-00054: resource busy and acquire with NOWAIT specified
ORA-00054: resource busy and acquire with NOWAIT specified
9260 views Less than a minute 0

Solution

There are some other sessions which are blocking this sessions

Check for locked_objects

select lo.session_id,lo.oracle_username,lo.os_user_name,lo.process,do.object_name, decode(lo.locked_mode,0, 'None',1, 'Null',2, 'Row Share (SS)', 3, 'Row Excl (SX)',4, 'Share',5, 'Share Row Excl (SSX)',6, 'Exclusive', to_char(lo.locked_mode)) mode_held from v$locked_object lo, dba_objects do where lo.object_id = do.object_id order by 1,5;

Oracle OS Process Table

Sid Username Username ID Locked MODE_HELD
----- ---------- ---------- ------------------------ ------------------------------ ---------------
645 DBACLASS oracle 1305 DBATEST Row Excl (SX)

There is an exclusive lock on the table & then try to Kill the session so that lock will be release

SQL> select sid,serial#,username,status from v$session where sid=645;

SID SERIAL# USERNAME STATUS
--------- ---------- -------
645 45877 DBACLASS INACTIVE

SQL> alter system kill session '645,45877' immediate;
System altered.

Now re-run the create create statement.

SQL> create table MIR.T_TRANSACTION(C_ID VARCHAR2(255 CHAR) not null primary key);
Table created.

=====Hence tested & verified=====