While creating an index or doing DDL change on a table, getting resource busy error.



SQL> create index IND_TEST on DBATEST ( OBJECT_ID);
create index IND_TEST on DBATEST ( OBJECT_ID)
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired



There are some other sessions which are blocking this sessions

1. Check for locked_objects

select lo.session_id,lo.oracle_username,lo.os_user_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
----- ---------- ---------- ------------------------ ------------------------------ ---------------
  271 DBACLASS   oracle     3323                     DBATEST                        Row Excl (SX)

We can see, there is an exclusive lock on the table.
Kill the session, to release the lock

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

       SID    SERIAL# USERNAME                       STATUS
---------- ---------- ------------------------------ --------
       271       1409 DBACLASS                       INACTIVE

SQL> alter system kill session '271,1409' immediate;

System altered.

Now re-run the create index statement,

SQL> create index IND_TEST on DBATEST ( OBJECT_ID);

Index created.