How to find the locked objects and Kill the Session in Oracle

Step-1 Run the following SQL query to find out the list of objects that has been locked

SELECT aob.object_name
,aob.object_id
,b.process
,b.session_id
FROM all_objects aob, v$locked_object b
WHERE aob.object_id = b.object_id

OR

SELECT aob.object_name
,aob.object_id
,b.process
,b.session_id
FROM all_objects aob, v$locked_object b
,V$session a
WHERE aob.object_id = b.object_id
and a.sid=b.session_id ;


Step-2 Now run the following SQL query with session id (from step-1)

SELECT SID, SERIAL#  FROM v$session WHERE SID = <SESSION_ID>
Note <SID> <SERIAL#>

 
Step-3 Run the following Query to kill the session with session_id and Serial no (from step-2)

ALTER SYSTEM KILL SESSION '<SID> ,<SERIAL#>';

Regards,

Sukhwinder Singh