Unlock An Oracle Table

Question

What are the different techniques for Unlocking an oracle table?

What I Tried.

1. Get the object ID of the locked table:

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

2. Get the SID values for this ID:

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

3. Get the session values for these SIDs:

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

4. Kill the sessions causing the lock:

   ALTER SYSTEM KILL SESSION (SID,SERIAL#)

pair values from step 3 e.g. ALTER SYSTEM KILL SESSION '231,23454'

But The Problem is I have lot of tables which has been locked is there any other technique for unlocking the tables.

I am using SQLDeveloper Is there any direct option for unlocking it.

Solution 1

Option 1:

Well, one of the options is to rollback the locked SQL statement. For an instance,

lock table table1 in exclusive mode;  -- is what you should've used to lock the table.

To unlock: rollback;

Option 2:

To find username and sid:

select * from v$locked_object;

To find serial# using sid:

select * from v$session where sid= put the sid you found on v$locked_object;

To kill session:

alter system kill session 'sid, serial#'; -- Execute this command as sysdba as normal user won't have privilege. 

Solution 2

You can also try to execute just

COMMIT;

or

ROLLBACK;

in this session.

Comments

[MadMad666]

Does oracle autounlock an object after some time ?

[HugoTai]

commit work because i'm the one locking the table, forgot to commit