Got error "ORA-00054" when truncate table

Sometimes, we may got the following errors during truncate table, this issue may happened when the table is locked by some session(s). When the table is locked in database, we cannot perform any DDL on this table.

Therefore, try to find out which session(s) is locked this object currently, and then kill the session(s) that related to locking. Finally, truncate this table again.


SQL> TRUNCATE TABLE EMPLOYEES;
TRUNCATE TABLE EMPLOYEES
               *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired 


SQL> select * from v$locked_object where object_id = in (
  2  select object_id from dba_objects wuhere object_name = 'EMPLOYEES');

    XIDUSN    XIDSLOT     XIDSQN  OBJECT_ID SESSION_ID
---------- ---------- ---------- ---------- ----------
ORACLE_USERNAME                OS_USER_NAME
------------------------------ ------------------------------
PROCESS                  LOCKED_MODE
------------------------ -----------
        60         11     243914     164033         28
HR                        oracle
6944:5636                          3


SQL> select sid from v$lock where id1=164033;

       SID
----------
        28

SQL> select sid, serial# from v$session where sid=28;

       SID    SERIAL#
---------- ----------
        28       5921

SQL> alter system kill session '28,5921';

System altered.

SQL> select * from v$locked_object where object_id in (
  2  select object_id from dba_objects where object_name = 'EMPLOYEES');

no rows selected

SQL> TRUNCATE TABLE EMPLOYEES;
Table truncated.


SQL> SELECT COUNT(*) AS EMPLOYEES FROM EMPLOYEES;
 EMPLOYEES                                                                      
----------                                                                      
         0