Killing Locked Sessions in Oracle

Many a times user sessions are locked in some DML/DDL operation, than how to unlock those sessions because a new step or operation could not be done on the specific object that is locked.

Some really easy steps to unlock an object or kill a session in oracle are as follows:

To view locked sessions in oracle:

select oracle_username,object_id,session_id from v$locked_object;

ORACLE_USERNAME OBJECT_ID SESSION_ID
—————————— ———- ———-
Lock_1 321876 130
Lock_2 320431 130
Lock_3 320429 130

Now checking the object that are locked, and that are not accessible:

select object_name from dba_objects where object_id = 321876;
OBJECT_NAME
——————————————————————————–
Table_1

Now you know which object is being locked and what is the object id whole session is to be killed.

First retrieve the serial# of object ID:

select sid,serial# from v$session where sid=130;
SID SERIAL#
———- ———-
130 8655

Now killing this session:

alter system kill session ’130,8655′;
System altered.

Now you can check by executing the first query again to see whether the locked session is still locked or not.