Locks in Oracle Database

Locks are held by Oracle Database to maintain the integrity during the concurrent updates. Locks are held until the transaction in a session is committed or rollbacked. Oracle maintains row level lock in the tables that are recently being updated.

In the distributed transaction, the network connection also plays vital role during locking. Once locked transaction in this mode never released if the failure in completion of processing on the remote side. This may also result in the object lock so that complete system may be affected.

So, it is the database administrator job to look after the database as well as to monitor the network passage side by side. Usually Oracle Database administrator use
v$locked_object to monitor the lock objects in the oracle database. One can get the session information and object held in the lock by linking v$locked_object to
v$session and dba_objects. Use folowing query to check the lock held in Oracle Database.

select c.owner, c.object_name, c.object_type, b.sid, b.serial#, b.status, b.osuser, b.machine
from v$locked_object a , v$session b, dba_objects c
where b.sid = a.session_id
and a.object_id = c.object_id;

You may kill the session to release the lock by

alter system kill session 'SID,SERIAL#';

Oracle releases locks during the restart of the database.