How To – Find the locking object and release object lock

Many time we faced the performance issue because of Locks on rows and it cause the delay to finishing the other transactions.

So with the help of following steps you will able to find the locking objects and can release the object locks in Oracle Database.

Let us create the example to understand clearly

Terminal – 1

Step -1:  Here we have execute the Update Statement in the SCOTT Schema without Commit

Terminal – 2

Step -2:

Now we try to execute the Update Statement on the same row in the SCOTT Schema and see our statement is waiting to process.

Step- 3:

Now create a session with DBA privilege user to check which object is locked and its SID. Use the below given script (Script – 1) to find the locked object and it’s SID

Script -1

select object_name, s.sid, s.serial#, p.spid  
from v$locked_object l, dba_objects o, v$session s, v$process p
where l.object_id = o.object_id and l.session_id = s.sid and s.paddr = p.addr;

Example:

 

Step- 4:

Now you can release the lock using below given script (Script -2)

Script -2

ALTER SYSTEM KILL SESSION 'sid,serial#';

Example :

 

You are Done !!!