Does anybody have knowledge about database links between 10g and 11g?
Is a supported setup/operation? Is it feasible? What are the problems? Caveats?
I have used DB Links from 11g to 10g. No large issues.
Only caveat is the fact that 11g may use mixed-situation passwords and you might like to switch that off if you're attempting to connect from the 10g database for an 11g one.
Sometimes you will find problems, whenever a link from 11G to 10.2..4.
Oracle Support Doc ID 730423.1: Choose With Local Function and Remote Tables Utilizing a Dblink Dangles Because Of Enq DX.
ORA-01719 may also be tossed for those who have an outer join query in 11g which uses IN or OR and also the tables are now being recommended via a db_connect to 10g.
11g to 11g works along with 10g to 10g - just pops up should you db_link from 11g to 10g.
Particularly, I am presently seeing this problem using 11.2..one to ten.2..3 and 11.2..2 to 10.2..4. In addition to different O/S releases: Home windows and Solaris.
Run this within the target 10g and 11g databases:
create table u1 (c1 number); create table u2 (c1 number, c2 number); insert into u1 values (1); insert into u1 values (2); insert into u2 values (1,1); insert into u2 values (1,2); commit;
Create db_links (DB10, DB11) inside your 11g connecting database to both 10g and 11g linked databases.
Run these queries inside your 11g connecting database:
/* this will fail 11g to 10g*/ SELECT * FROM u1@DB10 a, (SELECT * FROM u2@DB10 WHERE c1 IN (1, 2, 3)) b WHERE a.c1 = b.c1(+); /* this will work 11g to 11g*/ SELECT * FROM u1@DB11 a, (SELECT * FROM u2@DB11 WHERE c1 IN (1, 2, 3)) b WHERE a.c1 = b.c1(+);
A view with dblink within the choose list can no more be utilized from another schema using a synonym (accustomed to operate in Oracle 10)
create or replace foo_view as select foo.id@link id --yes, I know this is stupid... legacy code from foo@link
On other DB user
create synonym foo_synonym for otherdb.foo_view
choose foo_synonym: "ORA-02019 connection description for remote database not found"
The answer would be to remove dblinks in the underlying view's choose clause (which should not be there to begin with):
create or replace foo_view as select foo.id id from foo@lin foo