How to solve ORA-04021: timeout occurred while waiting to lock object

Compile an object can give the following error

select
s.sid, s.serial#, p.spid,
s.username,
s.osuser,
s.program,
s.terminal,
s.module, s.action
, p.tracefile
, s.type
, s.logon_time
, s.state
, s.wait_time, s.seconds_in_wait, s.service_name
from
v$session s,
v$process p
where s.paddr = p.addr ;

Quire the process to find the session that still running
select
a.object,
a.type,
b.sid,b.serial#,s.inst_id,
b.username,
b.osuser,
b.program,
s.status, s.sql_exec_start
from v$access a, v$session b, gv$session s
where a.sid = b.sid
and a.owner = DECODE(UPPER(‘&1’), ‘ALL’, a.object, upper(‘&1’))
and a.object = DECODE(UPPER(‘&2’), ‘ALL’, a.object, upper(‘&2’))
and b.sid = s.sid
and b.serial# = s.serial#
AND s.status = ‘ACTIVE’
order by a.object , sql_exec_start nulls last ;

Find process sid (psid) : ps –ef | grep psid

Looking for any sub process :

in this case there are not child .
Kill session on db
SQL> ALTER SYSTEM KILL SESSION ‘sid,serial#’;
f you are working in a RAC environment, you can specified RAC node .
The parameter is optionally specify the INST_ID and you can find it when querying the GV$SESSION view.
Command to kill session with INST_ID option:
SQL> ALTER SYSTEM KILL SESSION ‘sid,serial#,INST_ID’;
Using base command it is possible to explicit IMMEDIATE clause, in this case the father has not child then I can:

1. kill DB session

SQL> ALTER SYSTEM KILL SESSION ‘198,50795 IMMEDIATE ;

1. kill session on server, as said it has not child then

1. Compile to package does not raise any lock !!

find out the error that you get. Apply the solution to solve it then continue your play 🙂
——————————————————————————————————-