Tuesday, January 4, 2011

Releasing locks

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;


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:


Best regards,


No comments:

Post a Comment