Oracle Joins Across Multiple Servers

You can create a link as:

CREATE PUBLIC DATABASE LINK "OtherDB" 
CONNECT
TO "UserName" IDENTIFIED by "Password" USING 'SID'

SELECT * FROM "OtherSchema"."TableName"@"OtherDB";

or

CREATE SYNONYM "otherTableName" FOR "OtherSchema"."TableName"@"OtherDB" 
SELECT * FROM "otherTableName"

( it’s easier to do this via the oracle enterprise manager http://192.168.X.X:1158/em Administration > Schema )

You need to establish the link as a user on the other instance that has read access to the tables you need to join. Then:

SELECT a.columns, ..., b.columns
 FROM yourTable a INNER JOIN otherdb.otherTable ON a.column = b.column
 WHERE ...
ORDER BY ...;

After
the link is establiched, you can treat it as one of your tables in the
selects, just append the link name to the front of the table name

Leave a Reply

Your email address will not be published. Required fields are marked *