Monday, September 12, 2011

ORA-04021: timed out occured while waiting to lock object

Scenario:
I came across this error whilst trying to recompile a PL\SQL package in the database. It took some time to show this errro message, until then the sqldeveloper was in a locked state.

My package was doing parallel processing of procedures, where we use jobs, chains, programs, steps etc... I triggered this parallel processing once, and closed this before it completes. After this whenever I am trying to edit the package I get this time out error.

Analysis:
When someone is trying to recompile a package and found it is hanging or waiting it may be because some one else is using it and a new attempt to use the package would find it locked.

It seems there may be a lock on package.

Let us find out the session that locks the packages and Kill those.

select * from v$locked_object
no rows selected

select a.sid,a.serial#,b.sql_text from v$session a, v$sql b
where a.sql_id=b.sql_id
and a.username='schema'

select sessionid,owner, name from dba_ddl_locks where name like 'test_pkg'
SESSIONID
OWNER
NAME
2174,schema,test_pkg

SELECT * from v$access where object='test_pkg';
SID
OWNER
OBJECT
TYPE

2174,SCHEMA,TEST_PKG,PACKAGE

select sid,serial# from v$session where sid=2174;
SID
SERIAL#
2174,23456

Kill this session:
alter system kill session '2174,23456' immediate;

system altered

Now you may be able to recombile your package.

No comments:

Post a Comment