Tuesday, 15 July 2014

How to Unlock the Locked Table in ORACLE


Oracle puts locks while performing any DDL or DML operation on oracle tables.When table locks is present on any tables in Oracle we cannot run DDL on those tables.

Some of the locks automatically set by oracle are RS and RX Locks.
SELECT … FOR UPDATE execution results in RS (row share) table lock. When you execute an INSERT, UPDATE or DELETE Oracle puts RX (row exclusive) table lock.

We have to kill the session which holds the lock in order to execute further operations. Follow the below steps to kill the session and forcibly unlock the table.

Let’s assume that 'EMP' table is locked,

SELECT object_id FROM dba_objects WHERE object_name='EMP';
 OBJECT_ID
----------
   7401242

If there are no locks present for the table 'EMP' this query won’t return any values.

SELECT sid FROM v$lock WHERE id1=7401242
SID
----------
   3434


SELECT sid, serial# from v$session where sid=3434

       SID    SERIAL#

---------- ----------

      3434      92193


ALTER SYSTEM KILL SESSION '3434,92193' ;
Once the session is killed you will be able to carry out any DDL activities on EMP table. Also you can check in TOAD if there are any active sessions associated to the SID that we killed, to make sure that the session has been killed.


If you like this post, please share it on google by clicking on the Google +1 button.

PLEASE CHECK OUT OUR LATEST BLOG on :

12 comments:

  1. What you presented is a theoretical scenario for a system with very few users and little activity.

    In real life there are systems with multiple DMLs going on in parallel so while you kill off the sessions currently holding locks on your object, you will get new sessions getting locks before you are through with your loop. Furthermore, I am not sure whether rollback operations for the updating DMLs in the killed sessions will not introduce new locks of their own on same objects.

    So I would say: nice observation, but of little practical use.

    ReplyDelete
  2. Thank you. It solved one of my issues :)

    ReplyDelete
    Replies
    1. You’re welcome!
      Please go through similar Oracle Posts @DWHLAUREATE:
      http://dwhlaureate.blogspot.in/2012/08/joins-in-oracle.html

      Delete
  3. thanks man, it solved my problem!

    ReplyDelete
    Replies
    1. You’re welcome!
      Please go through similar Oracle Posts @DWHLAUREATE:
      http://dwhlaureate.blogspot.in/2012/08/joins-in-oracle.html

      Delete
  4. Thanks for such simple solution:)

    ReplyDelete
    Replies
    1. You’re welcome!
      Please go through similar Oracle Posts @DWHLAUREATE:
      http://dwhlaureate.blogspot.in/2012/08/joins-in-oracle.html

      Delete
  5. Replies
    1. You’re welcome!
      Please go through similar Oracle Posts @DWHLAUREATE:
      http://dwhlaureate.blogspot.in/2012/08/joins-in-oracle.html

      Delete
  6. Thank you! Helped me a lot!

    ReplyDelete
  7. QUANTUM BINARY SIGNALS

    Get professional trading signals delivered to your cell phone every day.

    Start following our signals NOW & make up to 270% per day.

    ReplyDelete

Related Posts Plugin for WordPress, Blogger...

ShareThis