3 Replies Latest reply: Jun 8, 2011 1:29 PM by ramoradba RSS

    DBMS_LOCK package missing

    851881
      Hi all,


      The user is a developer. The Oracle release is 10.2.0.3.0.

      The user (PIN24) gets a problems because he can't see the SYS DBMS_LOCK package, he look just the synonym.

      select *
      from all_objects
      where object_name = 'DBMS_LOCK'

      OWNER OBJECT_NAME OBJECT_TYPE STATUS
      PUBLIC DBMS_LOCK SYNONYM VALID

      I check if SYS user has the packages:

      SQL> select * from all_objects where object_name = 'DBMS_LOCK';

      OWNER OBJECT_NAME
      ------------------------------ ------------------------------
      SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
      ------------------------------ ---------- -------------- -------------------
      CREATED LAST_DDL_ TIMESTAMP STATUS T G S
      --------- --------- ------------------- ------- - - -
      SYS DBMS_LOCK
      4384 PACKAGE BODY
      02-MAR-07 02-MAR-07 2007-03-02:16:38:30 VALID N N N

      SYS DBMS_LOCK
      4281 PACKAGE
      02-MAR-07 06-MAR-08 2007-03-02:16:38:19 VALID N N N

      OWNER OBJECT_NAME
      ------------------------------ ------------------------------
      SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
      ------------------------------ ---------- -------------- -------------------
      CREATED LAST_DDL_ TIMESTAMP STATUS T G S
      --------- --------- ------------------- ------- - - -

      PUBLIC DBMS_LOCK
      4282 SYNONYM
      02-MAR-07 02-MAR-07 2007-03-02:16:38:21 VALID N N N


      Previous, the user works fine, I try to export a good schema and import it on the user PIN24 but it don't works.

      What can I do in order to assign the packages to PIN24 user without the export utility?

      I try with

      SQL> GRANT execute ON DBMS_LOCK TO PIN24;
      GRANT execute ON DBMS_LOCK TO PIN24
      *
      ERROR at line 1:
      ORA-01031: insufficient privileges


      SQL>

      Thanks.
        • 1. Re: DBMS_LOCK package missing
          Barbara Boehmer
          You need to be connected to sys as sysdba when you try to grant execute on dbms_lock to the user.
          • 2. Re: DBMS_LOCK package missing
            851881
            Thank you.

            I connected with sys as sysdba in order to grant the execute DBMS_LOCK to user and now it works fine.

            SQL> GRANT execute ON DBMS_LOCK TO PIN24;

            Grant succeeded.


            But I have not solved the issue.

            The user PIN24 has a lot of invalid object that can't be compiled:

            SQL> ALTER PROCEDURE PIN24.PROC_DEFER_C_TAX_AR_ACCT_OND COMPILE;

            Warning: Procedure altered with compilation errors.

            SQL> show error;
            Errors for PROCEDURE PIN24.PROC_DEFER_C_TAX_AR_ACCT_OND:

            LINE/COL ERROR
            -------- -----------------------------------------------------------------
            22/2 PL/SQL: SQL Statement ignored
            38/4 PLS-00201: identifier 'EVENT_T' must be declared
            92/3 PL/SQL: Statement ignored
            93/4 PLS-00364: loop index variable 'R' use is invalid

            With the user PIN20 I can to compile without errors.

            How can I solve it?

            Thanks.

            Edited by: Millennium2k on Jun 3, 2011 8:24 AM
            • 3. Re: DBMS_LOCK package missing
              ramoradba
              We dont know what is EVENT_T in your procedure as we don`t have any procedure code here.

              Sriram