Problem Description:

 

While trying to re-create an Oracle Package, the execution took long time and finally the following error was thrown:

ora-04021 timeout occurred while waiting to lock object

 

Resolution:

 

This means that the package is being locked by another user.

 

To figure out the session that is locking the user, use the following queries:

 

select * from v$access where OWNER=’schema_account’ and OBJECT=’database_object_name’;

 

//take the ‘SID’ from the previous query and search for more details about the session with v$session

 

select * from v$session where SID=XX;

//where XX is the number found in the v$access query

 

These sessions can be killed using the command:

 

SQL> ALTER SYSTEM KILL SESSION ‘sid,serial#’;

 

Then try to re-create the package and boooooom……package re-created 😉

 

Cheers!