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.