This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 31st, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,783 Users
  • 2,269,776 Discussions
  • 7,916,827 Comments

Discussions

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

Verdi
Verdi Member Posts: 79 Blue Ribbon

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.

Tagged: