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.