How to Unlock the Locked Table in ORACLE

Your package in not getting compiled and its keep on running forever? Are you getting lock objects issue? Is Oracle throwing error as ORA-04021 :timeout occurred

If Yes then its not your connection issue or system issue. Its issue with the objects which you are trying to compile.

Oracle puts locks while performing any DDL or DML operation on oracle tables. When table locks is present on any tables in Oracle we cannot run DDL on those tables. Some of the locks automatically set by oracle are RS and RX Locks.

We have to kill the session which holds the lock in order to execute further operations. 

Follow the below steps to kill the session and forcibly unlock the table.

First we need to find which all objects are locked and then we need to kill the session and unlock the objects.

To find sid, serial# and process of locked object:

select a.sid||'|'|| a.serial#||'|'|| a.process
 from v$session a, v$locked_object b, dba_objects c
 where b.object_id = c.object_id
 and a.sid = b.session_id
 and OBJECT_NAME=upper('XX_OBJECT_NAME');

To find process holding the lock by passing table name:

select distinct a.process
 from v$session a, v$locked_object b, dba_objects c
 where b.object_id = c.object_id
 and a.sid = b.session_id
 and OBJECT_NAME=upper('XX_OBJECT_NAME');

To find blocking session and type of lock.

select l1.inst_id,l1.sid, ' IS BLOCKING ', l2.sid,l1.type,l2.type,l1.lmode,l2.lmode,l2.inst_id
 from gv$lock l1, gv$lock l2
 where l1.block =1 and l2.request > 0
 and l1.id1=l2.id1
 and l1.id2=l2.id2; 

How to kill blocking sessions:

To find sql_id from sid
SQL> select sql_id from v$session where sid=&sid;

To find sql_full text from sql_id
SQL > select sql_fulltext from gv$sql where sql_id ="&SLQ_ID";

To kill sessions:
SQL> alter session kill session 'sid,serial#' immediate;

Using above query you can easily find the locked objects or the session holding the locks into the database. Blocking locks are very common into the database so you must check and eliminate before it cause more damage to the database.