Re: Differences in 11.2.0.4 & 19.10 when accessing tables across a DBLink

Sorry, this was a bit of a false alarm. Although the remote user's current_schema was set to the schema that owns the tables, private synonyms were also needed on the remote side to make this work in PL/SQL on the local side.

Regards,
Doug

> On March 24, 2022 at 11:28 AM DOUG KUSHNER <dougk5_at_cox.net> wrote:
>
>
> We are testing scenarios in preparation for migrating a few interconnected databases from 11.2.0.4 to 19.10.
>
> One database has a package that performs a query (join) against two small tables in another database through a DBLink. The user that the DBLink connects to has select on these tables granted to it. Everything works when both databases are 11g. When the remote database is 19c, the package cannot access the tables, although running the same query in SQL across the DBLink is successful. BTW, none of the required privileges are granted through roles.
>
> The error is ORA-00942: table or view does not exist
>
> I found a workaround, which is creating private synonyms for the remote tables and changing the PL/SQL to access the synonym name rather than table_name_at_remote_db. Would still like to understand what changed in 19c to cause this issue. Has anyone here encountered this or similar issues with 19c?
>
> TIA,
> Doug
>
>

--
http://www.freelists.org/webpage/oracle-l