To kill locked sessions in Oracle

To view the locked session on oracle
SQL> select oracle_username,object_id,session_id from v$locked_object;
ORACLE_USERNAME OBJECT_ID SESSION_ID
------------------------------ ---------- ----------
AHSAN_REPORT 321876 130
AHSAN_REPORT 320431 130
AHSAN_REPORT 320429 130
AHSAN_REPORT 320427 130
AHSAN_REPORT 320415 130
AHSAN_REPORT 320413 130
6 rows selected.

Now you would like to see which objects are actually locked. For this do as follows:

SQL> select object_name from dba_objects where object_id = 321876;
OBJECT_NAME
--------------------------------------------------------------------------------
APR_MOSAIC_GEO_CODE_DIM

After being sure that this object indeed belongs to you, you can kill it by:

1) first obtain the object Id from session.

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

2) Kill the disturbing session.

SQL> alter system kill session '130,8655';
System altered.

Now you can verify that there are no more blocked objects.

SQL> select oracle_username,object_id,session_id from v$locked_object;
no rows selected

Sphere: Related Content