Differences in 11.2.0.4 & 19.10 when accessing tables across a DBLink
Date: Thu, 24 Mar 2022 11:28:52 -0700 (MST)
Message-ID: <752047661.751865.1648146532622_at_myemail.cox.net>
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-lReceived on Thu Mar 24 2022 - 19:28:52 CET