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

##########################
## Error
##########################

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

### Full Error

SQL> Truncate table SCOTT.EMP;
Truncate table SCOTT.EMP
                                *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

##########################
#  Error Occurred
##########################

Error occured while trying to truncate a table in 11.2.0.4.0 version

##########################
## Command Executed
##########################

Truncate table SCOTT.EMP;

**************************************** Step By Step Analysis ******************************************************

#########################################
# 1) Check who is using the object
#########################################

set lines 200
col owner for a15
col object_name for a25
col ORACLE_USERNAME for a15
select do.OWNER, do.OBJECT_NAME, do.OBJECT_ID, lo.SESSION_ID, lo.ORACLE_USERNAME, lo.OS_USER_NAME, lo.PROCESS, lo.LOCKED_MODE
from   dba_objects do, v$locked_object lo where  do.OBJECT_ID = lo.OBJECT_ID and    do.OWNER = 'SCOTT' and    do.OBJECT_NAME = 'EMP';

OWNER           OBJECT_NAME                OBJECT_ID SESSION_ID ORACLE_USERNAME OS_USER_NAME                   PROCESS                  LOCKED_MODE
--------------- ------------------------- ---------- ---------- --------------- ------------------------------ ------------------------ -----------
SCOTT EMP             16036        105 SYS             oracle                         29849                              3
SCOTT EMP             16036        513 SYS             oracle                         18361                              3

=====================================================================================================================

#########################################
# 2) Session Details
#########################################

set lines 200
col username for a25
col osuser for a15
col program for a40
col machine for a20
col status for a10
select inst_id,sid,serial#,username,osuser,program,machine,status,floor(last_call_et/60) "AIM",logon_time from gv$session where sid in ('105','513') order by last_call_et desc;

INST_ID     SID    SERIAL# USERNAME                  OSUSER          PROGRAM                                  MACHINE              STATUS            AIM LOGON_TIM
------- ------- ---------- ------------------------- --------------- ---------------------------------------- -------------------- ---------- ---------- ---------
      1     513       5889 SYS                       oracle          sqlplus@SIDHOST (TNS V1-V3)             SIDHOST             INACTIVE         6983 11-AUG-16
      1     105      12623 SYS                       oracle          sqlplus@SIDHOST (TNS V1-V3)             SIDHOST             INACTIVE         6902 11-AUG-16

=====================================================================================================================

#########################################
# 3) See what queries they are running
#########################################

Select sql_text from v$sqlarea where (address,hash_value) in (select sql_Address,sql_hash_value from v$session where sid=105);

SQL> Select sql_text from v$sqlarea where (address,hash_value) in (select sql_Address,sql_hash_value from v$session where sid=513);

no rows selected

SQL> Select sql_text from v$sqlarea where (address,hash_value) in (select sql_Address,sql_hash_value from v$session where sid=105);

no rows selected

SQL>

#########################################
# 4) Reason for Failure
#########################################

From output of Step 1), we can see that those two sessions have tried accessing that object. Possibly modifying that table but its INACTIVE. While checking found that one of the developer logged in and tried some delete statement and didnt log off.

=====================================================================================================================

##########################
## Solution
##########################

Kill those sessions if not required, or request the users to logoff from the sessions.

In my case i've killed the sessions as its not required.

Alter system kill session '513,5889' immediate;
Alter system kill session '105,12623' immediate;

SQL> Alter system kill session '513,5889' immediate;
System altered.
SQL> Alter system kill session '105,12623' immediate;
System altered.

=====================================================================================================================
Truncate command worked good after clearing the session.
=====================================================================================================================

=====================================================================================================================
 Comments Are Always welcome
=====================================================================================================================