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;
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;
Now killing this session:
alter system kill session ‘130,8655’;
Now you can check by executing the first query again to see whether the locked session is still locked or not.