Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Cannot compile view from another schema which view uses private db link

VerdiAug 31 2021

Hello,
Oracle version: Oracle Database 11g Enterprise Edition 11.2.0.4.0 64bit Production
Installed on Linux Red Hat
User USER1 has a private link DB_LINK1.
User USER1 has also a view USR1_VIEW1 which queries several tables from USER1's schema, plus several tables from the remote database that is accessed via the private link DB_LINK1.
There is second user USER2 who has the following roles granted (no other permissions granted):
SELECT_CATALOG_ROLE, CONNECT

USER2 tries to do the following from SqlPlus:
ALTER SESSION SET CURRENT_SCHEMA = USER1;
ALTER VIEW USR1_VIEW1 COMPILE;

User2 is able to compile if the view accesses only one table from the remote DB.
User2 is NOT able to compile if the view accesses 2 or more tables joined together from the remote DB. The error then is:
ORA-02019: connection description for remote database not found

As I said the link is privately owned by USER1.
Please can you advise why is this happening or what I might be doing wrongly? I tried to google but I haven't found the answer so far. Thank you for your time.

Comments

Post Details

Added on Aug 31 2021
0 comments
509 views