ORA-00990: missing or invalid privilege

I got ” ORA-00990: missing or invalid privilege ”  error in Oracle database.

ORA-00990: missing or invalid privilege

Details of error are as follows.

ORA-00990: missing or invalid privilege

Cause: No privileges were specified in a GRANT privilege statement, or one of the specified 
privileges is invalid.

Action: Enter one or more valid privileges such as SELECT, INSERT, DELETE, UPDATE, ALTER, 
INDEX, REFERENCES, or ALL. More than one privilege may be granted by entering the privileges
 in a list separated by commas (,) or by specifying the keyword ALL to grant all privileges.

missing or invalid privilege

This ORA-00990 error is related with the Priviliges of user. No privileges were specified in a GRANT privilege statement, or one of the specified privileges is invalid.

To solve this error, Enter one or more valid privileges such as SELECT, INSERT, DELETE, UPDATE, ALTER, INDEX, REFERENCES, or ALL. More than one privilege may be granted by entering the privileges in a list separated by commas (,) or by specifying the keyword ALL to grant all privileges as follows..

select insert,update,delete,select on TABLE_NAME to USERNAME;

You should read the following post to learn more details about How to grant or revoke priviliges in Oracle.

How to Grant and Revoke Privileges | Create and Drop any User in Oracle Database

Other case is as follows.

move_qt_pkg.move_queue_table procedure execution failes with ORA-00990 error.

SQL> exec move_qt_pkg.move_queue_table(‘APPLSYS’,’WF_JMS_IN’,’APPS_TS_QUEUES’,’APPS_TS_QUEUES_REORG’)
BEGIN move_qt_pkg.move_queue_table(‘APPLSYS’,’WF_JMS_IN’,’APPS_TS_QUEUES’,’APPS_TS_QUEUES_REORG’); END;

*
ERROR at line 1:
ORA-00990: missing or invalid privilege
ORA-06512: at “SYS.DBMS_REDEFINITION”, line 884
ORA-06512: at “SYS.DBMS_REDEFINITION”, line 2010
ORA-06512: at “AQMOVE.MOVE_QT_PKG”, line 509
ORA-06512: at “AQMOVE.MOVE_QT_PKG”, line 108

Customer or the applicaiton has granted “ENQUEUE/DEQUEUE WITH GRANT OPTION” privileges to “APPS” user on the AQ table owned APPLSYS.

The move_qt package is trying to maintain this privilege when creating a intermediate Queue table for reorg purpose.

Example output from the Trace file:

 SQL>  alter session set events ‘990 trace name errorstack level 3’;
SQL>  alter session set events ‘10046 trace name context forever,level 12’;
———– reproduce the issue
SQL>  exit;

dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)
—– Error Stack Dump —–
ORA-00990: missing or invalid privilege
—– Current SQL Statement for this session (sql_id=fxafftt82k2hj) —–
GRANT ENQUEUE, DEQUEUE ON “APPLSYS”.”QT_INT” TO “APPS” WITH GRANT OPTION

—– PL/SQL Stack —–
—– PL/SQL Call Stack —–
object      line  object
handle    number  name
70000017d6a1680       278  package body SYS.DBMS_REDEFINITION
70000017d6a1680       750  package body SYS.DBMS_REDEFINITION
70000017d6a1680       872  package body SYS.DBMS_REDEFINITION
70000017d6a1680      2010  package body SYS.DBMS_REDEFINITION
70000017e8b1f40       509  package body AQMOVE.MOVE_QT_PKG
70000017e8b1f40       108  package body AQMOVE.MOVE_QT_PKG
70000017d2a4988         1  anonymous block

APPLIES TO:

Oracle Database – Enterprise Edition – Version 11.2.0.1 and later
Oracle Database Exadata Cloud Machine – Version N/A and later
Oracle Cloud Infrastructure – Database Service – Version N/A and later
Oracle Database Cloud Exadata Service – Version N/A and later
Oracle Database Exadata Express Cloud Service – Version N/A and later
Information in this document applies to any platform.

SYMPTOMS

move_qt_pkg.move_queue_table procedure execution failes with ORA-00990 error.

SQL> exec move_qt_pkg.move_queue_table('APPLSYS','WF_JMS_IN','APPS_TS_QUEUES','APPS_TS_QUEUES_REORG')
BEGIN move_qt_pkg.move_queue_table('APPLSYS','WF_JMS_IN','APPS_TS_QUEUES','APPS_TS_QUEUES_REORG'); END;

*
ERROR at line 1:
ORA-00990: missing or invalid privilege
ORA-06512: at "SYS.DBMS_REDEFINITION", line 884
ORA-06512: at "SYS.DBMS_REDEFINITION", line 2010
ORA-06512: at "AQMOVE.MOVE_QT_PKG", line 509
ORA-06512: at "AQMOVE.MOVE_QT_PKG", line 108

CAUSE

Customer or the applicaiton has granted “ENQUEUE/DEQUEUE WITH GRANT OPTION” privileges to “APPS” user on the AQ table owned APPLSYS.

The move_qt package is trying to maintain this privilege when creating a intermediate Queue table for reorg purpose.

Example output from the Trace file:

  SQL>  alter session set events '990 trace name errorstack level 3';
  SQL>  alter session set events '10046 trace name context forever,level 12';
   ----------- reproduce the issue
  SQL>  exit;




dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)
----- Error Stack Dump -----
ORA-00990: missing or invalid privilege
----- Current SQL Statement for this session (sql_id=fxafftt82k2hj) -----
GRANT ENQUEUE, DEQUEUE ON "APPLSYS"."QT_INT" TO "APPS" WITH GRANT OPTION

----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
 object      line  object
 handle    number  name
70000017d6a1680       278  package body SYS.DBMS_REDEFINITION
70000017d6a1680       750  package body SYS.DBMS_REDEFINITION
70000017d6a1680       872  package body SYS.DBMS_REDEFINITION
70000017d6a1680      2010  package body SYS.DBMS_REDEFINITION
70000017e8b1f40       509  package body AQMOVE.MOVE_QT_PKG
70000017e8b1f40       108  package body AQMOVE.MOVE_QT_PKG
70000017d2a4988         1  anonymous block

SOLUTION

To avoid the move_qt package failure we need to remove this external user grant privielges and then regrant the privilege once the queue table is moved successfully to New tablespace.

Example:

SQL> select * from dba_tab_privs where table_name like '%WF_JMS_OUT%';

GRANTEE                        OWNER                          TABLE_NAME                     GRANTOR                        PRIVILEGE                                GRA HIE
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------------------------------- --- ---
APPS                           APPLSYS                        WF_JMS_OUT                     APPLSYS                        ENQUEUE                                  YES NO
APPS                           APPLSYS                        WF_JMS_OUT                     APPLSYS                        DEQUEUE                                  YES NO



SQL> EXECUTE DBMS_AQADM.REVOKE_QUEUE_PRIVILEGE(    privilege => 'ENQUEUE', queue_name => 'APPLSYS.WF_JMS_OUT',     grantee => 'APPS');

SQL> EXECUTE DBMS_AQADM.REVOKE_QUEUE_PRIVILEGE(    privilege => 'DEQUEUE', queue_name => 'APPLSYS.WF_JMS_OUT',     grantee => 'APPS');

SQL> select * from dba_tab_privs where table_name like '%WF_JMS_OUT%';

SQL> exec move_qt_pkg.move_queue_table('APPLSYS','WF_JMS_OUT','APPS_TS_QUEUES','APPS_TS_QUEUES_REORG')




SQL> EXECUTE DBMS_AQADM.GRANT_QUEUE_PRIVILEGE(    privilege => 'ENQUEUE', queue_name => 'APPLSYS.WF_JMS_OUT',     grantee => 'APPS');

SQL> EXECUTE DBMS_AQADM.GRANT_QUEUE_PRIVILEGE(    privilege => 'DEQUEUE', queue_name => 'APPLSYS.WF_JMS_OUT',     grantee => 'APPS');

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )