Return
-
Title
User defined collection is failing on pluggable database - error: "ORA-00942: table or view does not exist" -
Description
When creating a user defined collection (UDC) for an Oracle pluggable database (PDB) and using the agent credential which is an Oracle common user, the connection test failed with:"ORA-00942: table or view does not exist"When using another user with access to the PDB the following error appears:"ORA-01017: invalid user/password" -
Cause
The Oracle agent always connects to the container database (CDB) and here a common user is needed (for example c##foglight). But this common user does not have access to the PDB, so the error message is correct.Using a user from the PDB does not work either as that one does not have access to the CDB. -
Resolution
As a workaround please create a database link (DBLink) in the CDB, so that the common user is able to access the tables in the PDB.
To create a DBLink you can use the following syntax:
"create public database link MyPDB using ‘MyPDB’;"
Further details can be found in the Oracle documentation.
-
Additional Information
This blog post might be helpful too