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
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:
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:
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.
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.