Popular Articles

From the 8th century the Muslim invaders began to make forays into Hindustan and by the 12th century the entire North India and the throne at Delhi had
Privacy is an important thing in every section. Same goes for mobile phones too. People always have something to hide from others on their mobile phone
Riding a bike in the hills is a matter of great pleasure. It is also an adventure, to be relished long after it has past.  Riding a bike in the

Latest Articles

Privacy is an important thing in every section. Same goes for mobile phones too. People always have something to hide from others on their mobile phone
Subhas Chandra Bose is one of the towering figures of the freedom movement. The fact that he differed from Gandhi and Nehru is well known and documented,
From the 8th century the Muslim invaders began to make forays into Hindustan and by the 12th century the entire North India and the throne at Delhi had

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.

    SELECT sid FROM v$lock WHERE id1=OBJECT ID FROM STEP1

  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'
  • No comments found