Oracle put locks on tables when any DML/DDL operations performing. Generally, some internal processes accesses the data and locks the table. So if you want to forcibly unlock the table you can follow the below steps.

  1. Get the Object ID from the dba_objects table. All of your tables will be assigned a unique id and stores a reference in dba_objects table. This table is useful in finding the basic information about each object stored on your Oracle instance, and not only tables.
    Here is the SQL statement to get the object id of the locked table.

    SELECT object_id FROM dba_objects WHERE object_name='YOUR TABLE NAME';

  2. Get the SID for the corresponding Object ID we have obtained from the step 1. You can get the SID from v$lock table. Here is the query.


  3. Note down all sid values from step 2. Get the sid, serial number pairs from the v$session table using the SID obtained from the step 2. Here is the query.

    SELECT sid, serial# from v$session where sid in (COMMA SEPARATED LIST OF SIDs FROM STEP2.)

  4. Note down the information from step 3. Now we have to kill the sessions that locked the object we require. Below is the query to do that.

    ALTER SYSTEM KILL SESSION (SID,SERIAL#) pair values from step 3
    e.g. ALTER SYSTEM KILL SESSION '231','23454'

Like it on Facebook, Tweet it or share this article on other bookmarking websites.

Comments (0)

There are no comments posted here yet

Which Song(s) Do you Lyk, most in DHOOKUDU?

Nice music composition from Thaman....He has been giving good music all the time.

No answer selected. Please try again.
Please select either existing option or enter your own, however not both.
Please select minimum 0 answer(s) and maximum 6 answer(s).
[{"id":"21042","title":"Dethadi Dethadi","votes":"24","type":"x","order":"0","pct":21.82,"resources":[]},{"id":"21043","title":"Nee Dhookudu","votes":"27","type":"x","order":"0","pct":24.55,"resources":[]},{"id":"21044","title":"Poovai Poovai","votes":"29","type":"x","order":"0","pct":26.36,"resources":[]},{"id":"21045","title":"Adara Adara","votes":"10","type":"x","order":"0","pct":9.09,"resources":[]},{"id":"21046","title":"Chulbuli","votes":"13","type":"x","order":"0","pct":11.82,"resources":[]},{"id":"21047","title":"Guruvaaram","votes":"7","type":"x","order":"0","pct":6.36,"resources":[]}] ["#ff5b00","#4ac0f2","#b80028","#eef66c","#60bb22","#b96a9a","#62c2cc"] ["rgba(255,91,0,0.7)","rgba(74,192,242,0.7)","rgba(184,0,40,0.7)","rgba(238,246,108,0.7)","rgba(96,187,34,0.7)","rgba(185,106,154,0.7)","rgba(98,194,204,0.7)"] 350
bottom 200
No married couple wants to end up getting divorced. It is not like they have planned for it. They try to put up with their partners for as long as they
Due to our modern lifestyle, we feel that digestion related disorders are a common problem. Thus, we neither give importance to them nor seek any help
The bond of marriage brings the two people together. Initially, everything may seem okay and both of them slowly start discovering each other in the journey.
Introduction:  The oracle database server is an object-relational database management system. This write-up deals with practical aspects of compatibility
Hello and Welcome back to SQL beginners tutorial part 2. In the previous article, as a first part in the series we learnt till how to insert the data in
This Whole world is running because of data and its increasing at an exponential rate. You have data around you everywhere and all the time. Even your
  The types of users and their roles and responsibilities at a site can vary. A small site can have one database administrator who administers the database
A database is a logically coherent collection of data with some inherent meaning, representing some aspect of real world and which is designed, built and