Identify and release locks on Object before using DDL commands

Note:  Proofread any scripts before using. Always try scripts on a test instance first. This Blog is not responsible for any damage
 
Some times we get an error while running a DDL statement on a table. something like below:

SQL> drop table test.aa;
drop table test.aa
*
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index on temporary table already in use

(or)

SQL> truncate table test.aa;
truncate table test.aa
                         *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

This happens because some other session is using this table or having a lock on this table.

Following is the simple procedure to kill the session holding the lock on this table and drop or truncate the table. Note that this should be done only if you are sure that this table is no more required and all sessions holding lock on this table can be deleted

1. Get the object ID of the table to be dropped -

SQL> select OWNER,OBJECT_NAME,object_id from dba_objects where object_name = 'AA';
OWNER      OBJECT_NAME  OBJECT_ID
---------- ----------- ----------
PUBLIC              AA    3970035
TEST                AA    3735492

2. Get the session ID which is holding lock on this object from v$locked_object view -

SQL> select OBJECT_ID, SESSION_ID, ORACLE_USERNAME, PROCESS from v$locked_object where OBJECT_ID = 3735492;

 OBJECT_ID SESSION_ID ORACLE_USERNAME            PROCESS
---------- ---------- --------------- ------------------
   3735492       1124 MSC               4092@AKPRADH-LAP

3. Get the serial# of the SID using v$session -

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

       SID    SERIAL#
     ----- ----------
      1124      51189

4. Kill the session by connecting as sysdba and try dropping the table -

SQL> alter system kill session '1124, 51189';

System altered.

5. Once the locks are removed, you should be able to drop and truncate the table -

SQL> drop table test.aa;

Table dropped.

(or)

SQL> truncate table test.aa;

Table truncated