ora-04021 timeout occurred while waiting to lock object package

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!

2 thoughts on “ora-04021 timeout occurred while waiting to lock object package

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s