Register Login

ORA-00942: Table or view does not exist, when accessing Oracle dictionary tables

Updated May 18, 2018

If the database returns the following Oracle error when you access the database dictionary, ORA-00942: table or view does not exist even though the object is clearly contained in the database. It is due to the relevant database user does not have the rights to access the objects in the Oracle dictionary. The user does not have the SELECT_CATALOG_ROLE role or this role is inactive.
 

Solution:

Use the following procedure to check whether the role exists (with SQL*Plus, for example):
   % sqlplus
   [...]
   SQL> connect / as sysdba
   Connected.
   SQL> select granted_role, default_role
     2> from   dba_role_privs
     3> where  grantee='<username>';

   GRANTED_ROLE                   DEF
   ------------------------------ ---
   CONNECT                        YES
   RESOURCE                       YES
   SELECT_CATALOG_ROLE            YES

   SQL>

where <username> is the name of the relevant database user.

If the system does not output the SELECT_CATALOG_ROLE line, this means that the database user does not have the relevant role. Use the following command to assign the role to the user:
   SQL> grant SELECT_CATALOG_ROLE to <username>;

   Grant succeeded.

   SQL>

and use the above statement to recheck the settings.

If the role exists, but the value of the DEFAULT_ROLE column is set to NO, change this using:
   SQL> alter user <username> default role <role1>, <role2>, ...;

   User altered.

   SQL>

where <role1> is the SELECT_CATALOG_ROLE and you then have to specify all other user roles that were already set to YES.

The database user can now view the objects of the Oracle dictionary.

Get More Questions and Answers with Explanation at SAP BASIS Forums.


×