Query two tables from different schema

Question

I have two different schemas in Oracle (say S1, S2) and two tables in those schemas (say S1.Table1, S2.Table2). I want to query these two tables from schema S1.

Both S1 and S2 are in different databases. From DB1 - Schema S1, I want to do something like this,

select T1.Id
  from S1.Table1 T1
     , S2.Table2 T2 
 Where T1.Id = T2.refId

I know one way of doing this would be to create a DB Link for the second schema and use it in the query. Sadly, I don't have the privileges necessary to create a DB link. Is there some way to do this without DB link? For example, in TOAD you can compare two schema objects. But again, two schema objects and it is general comparison.

Any ideas, suggestions are greatly appreciated. Thanks in advance.

Answer 1

You won't need a database link if the two schemas are in the same database.

Your query should work from schema S1, provided S1 has been granted the SELECT privilege on S2.table2 (from a dba account or from the S2 schema: GRANT SELECT ON S2.Table2 TO S1).

Answer 2

DB Links are pretty much the name of the game here. If you can't get one created on your own, then check if there are any public DB links that you could use.

It's also possible that your DBAs will be willing to have one of their DB Links used to create a materialized view of S2.Table2 on the S1 instance.

Another option might be web services, but my guess is you'd run into much more administrative issues there than you would with a simple DB link. Consider those only if there are good reasons for no links (example: two separate organizations that don't want to open firewall holes between their databases).

Failing those, you're getting into really ugly territory but you might be able to make something work. For example:

• Open up both from a tool that can read from multiple connections at once and do the join there. Access. Toad for Data Analysis, whatever.
• Use a tool like Toad to copy S2.Table2 to your own schema ("create in another schema" followed by "copy data to another schema")
• If you have, or can get, complementary directory objects defined on both servers, create a Materialized View of S2 as an external table in a directory which can be written from S2 and read from S1.

You really don't want to maintain any of these solutions over the long term, though.

Answer 3

You don't specify whether this feature is needed as part of production code, or if you're trying to join the two tables to perform some one-time analysis. If it's the latter, you can use Microsoft Access to create a local mdb file that contains linked tables to the two databases, then write a local Access query that refers to those two tables. You could then use that mdb as a data source for various reporting tools.

The queries might not use indexes as efficiently as a native Oracle db link, but it would be better than nothing.

edit: Nevermind - I see that this was already suggested above.

Answer 4

Use the CREATE DATABASE LINK statement to create a database link. A database link is a schema object in one database that enables you to access objects on another database.

a little off topic, but you might want to use the newer join syntax:

SELECT
    T1.Id
    FROM S1.Table1            T1
        INNER JOIN S2.Table2  T2 ON T1.Id = T2.refId

All join conditions appear in the "ON" clause, and filter conditions appear in the "WHERE".

This new style makes LEFT/RIGHT joins easier to read and understand. Also, I'm not that familiar with Oracle (it has been many years since I worked on it), but with SQL Server, I've seen problems when the old join style and new join style were mixed together in a query using views.

Answer 5

You could create a java stored proc that connects to the other database and executes a select on the other database via JDBC. The java stored proc has to return a collection. You could join this collection via a select from table(...) with the table in your own database.

See here for a roughly similar solution.

I think this approach will be slow and complicated because you have to do a lot of coding and you have to create a pl/sql wrapper for your java stored proc.

It is better to create a database link.