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