Home > Oracle > Oracle Compile Invalid Object – PLS-00201: identifier ‘DBMS_LOCK’ must be declared

Oracle Compile Invalid Object – PLS-00201: identifier ‘DBMS_LOCK’ must be declared

March 29th, 2013

Just finished importing a complete oracle database dump with impdp.

Immediately after the import I checked for invalid objects and of course some were present.

In order to get the complete list of schema objects that were invalid I ran the following:

 

SQL>SELECT OBJECT_NAME, OBJECT_TYPE FROM USER_OBJECTS WHERE STATUS=’INVALID’;

 

OBJECT_NAME                                         OBJECT_TYPE
————————————————————————

STOMA_USER_INSTANCES                 PACKAGE BODY

SQL>ALTER PACKAGE STOMA_USER_INSTANCES COMPILE BODY;

PLS-00201: identifier ‘DBMS_LOCK’ must be declared

 

Looks like a missing privilege for my STOMA user.

 

Resolution

 

Connect to the instance as SYS as SYSDBA

 

SQL>GRANT EXECUTE ON DBMS_LOCK TO STOMA;

Grant succeeded.

 

Now connect with the schema user and try recompiling the package body again.

 

SQL>ALTER PACKAGE STOMA_USER_INSTANCES COMPILE BODY;

package stoma_user_instances altered;

 

Done.

 

Categories: Oracle Tags:
  1. April 29th, 2013 at 11:09 | #1

    SQL can execute iqreues against a database, retrieve,update,insert data from a database and empty refer to an empty result of no rows and NULL refer to a table with one row holding a NULL value.

  2. May 2nd, 2013 at 21:02 | #2

    Hey There. I found your blog using msn. This is
    an extremely well written article. I will make sure to bookmark
    it and return to read more of your useful info. Thanks for the post.
    I will definitely return.

  3. June 13th, 2013 at 02:55 | #3

    Hi there, Chery!
    What’s the relevance of that?

Comments are closed.