Unlocking the locked table

Some times we get an error while running a DDL statement on a table. something like below

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

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 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 object_id from dba_objects where object_name = 'AA';
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;
---------- ---------- ------------------------------ ------------------------
   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.
SQL> drop table aa;
Table dropped.
Once the locks are removed, you should be able to drop the table.
Hope this helps !!

8 thoughts on “Unlocking the locked table

  1. HI …..

    I have the same prob i want to unlock the table login from oracle 10g but i can’t acess it plz help …

    Thank You

    1. Thanks a lot for these steps. I was in an immediate need to unlock a table, and your steps just helped me at right moment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s