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 🙂

——————————————————————————————————-

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *