Releasing locks

Hi,
Steps for releasing lock on a table:

Step1:To verify the lock object Here are the important queries:
---------------------------------------------------------------

Find the tables(objects) which are locked:

SELECT o.owner, o.object_name, o.object_type, o.last_ddl_time, o.status, l.session_id, l.oracle_username, l.locked_mode
FROM dba_objects o, gv$locked_object l
WHERE o.object_id = l.object_id;

Find the session which are holding the lock:

select type, id1, id2, lmode, request
from v$lock
where sid = (select sid from v$mystat where rownum = 1)
;

Step 2:Killing the session holding the lock.
---------
Find the serial# for the sessions holding the lock:

SQL> select SERIAL# from v$session where SID=667;

SERIAL#
----------
21091

SQL> alter system kill session '667,21091';

System altered.
Usually the locks are released when the DML statements are rollback or commited.There are 2 lock modes for row level locks:

1)3 =>row exclusive lock
2)2=>row shared lock

For complete details and concept building on Oracle locks,Read the below
article from Jonathan levis:

http://jonathanlewis.wordpress.com/2010/02/15/lock-horror/


Best regards,

Rafi.