CODEWITHSUNDEEP
oracle-databaseoracle19c
SRJ
SRJ

Reputation: 309

Oracle 19C - Error trying to grant execute on DBMS_LOCK

Updating some scripts from 18 to 19, but getting this

Have also tried just DBMS_LOCK instead of SYS.DBMS_LOCK

SQL> GRANT EXECUTE ON SYS.DBMS_LOCK to myuser;
GRANT EXECUTE ON SYS.DBMS_LOCK to myuser
                     *
ERROR at line 1:
ORA-04042: procedure, function, package, or package body does not exist

sqlplus "sys/ChangeMe123! AS SYSDBA"

Note - other grants worked

SQL> GRANT ALTER SESSION TO myuser;

Grant succeeded.

SQL> GRANT CREATE PROCEDURE TO myuser;

Grant succeeded.

SQL> GRANT CREATE SEQUENCE TO myuser;

Grant succeeded.

SQL> GRANT CREATE SESSION TO myuser;

Grant succeeded.

SQL> GRANT CREATE MATERIALIZED VIEW TO myuser;

Grant succeeded.

SQL> GRANT CREATE TABLE TO myuser;

Grant succeeded.

SQL> GRANT CREATE TRIGGER TO myuser;

Grant succeeded.

SQL> GRANT CREATE VIEW TO myuser;

Grant succeeded.

SQL> GRANT CREATE ANY SYNONYM TO myuser;

Grant succeeded.

SQL> GRANT DROP ANY SYNONYM TO myuser;

Grant succeeded.

SQL> GRANT SELECT ANY DICTIONARY TO myuser;

Grant succeeded.

SQL> GRANT EXECUTE ON DBMS_LOCK to myuser; GRANT EXECUTE ON DBMS_LOCK to myuser

Upvotes: 0

Views: 3487

Answers (1)

Suresh
Suresh

Reputation: 442

DBMS_LOCK.SLEEP was deprecated replaced with DBMS_SESSION.SLEEP but still available in 19c for backwards compatibility.Verify if object exists

SQL> select object_name,object_type,owner from dba_objects
  2  where object_name='DBMS_LOCK';

OBJECT_NAME                    OBJECT_TYPE             OWNER
------------------------------ ----------------------- ------------------------------
DBMS_LOCK                      PACKAGE                 SYS
DBMS_LOCK                      PACKAGE BODY            SYS
DBMS_LOCK                      SYNONYM                 PUBLIC

If above query returns nothing then run the dbmslock script as a sysdba that creates above package

sql>@?/rdbms/admin/dbmslock

Upvotes: 1

Related Questions