Query more than one database?

Question

What's the way to query more than one database using SQL*Plus?

In MySQL it's possible to do something like this :

create table WK_LINK_JOINT_IDEOREQ AS
select k.constraint_name cn, k.table_name tl, l.column_name lc
     , k.referenced_table_name tg, k.column_name cg, l.referenced_table_name td
     , l.referenced_column_name cd
  from information_schema.KEY_COLUMN_USAGE k

It's just an example, it's not complete : but as you can see, we are working on two databases, INFORMATION_SCHEMA and another one.

I want to do somthing like this using SQL*Plus, but the problem is that when we connect using SQL*Plus, we specify the database (SID) which means that the others are not accessible.

Is there a way to do it?

ma be helpfull http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_5005.htm — Josef Prochazka, Jun 26 '13 at 14:00
Josef's suggestion is correct - database links are the mechanism that Oracle provides for cross database queries. — marceljg, Jun 26 '13 at 14:09

score 1 · Accepted Answer · edited May 23 '17 at 10:32

Oracle has a different interpretation of DATABASE from MySQL. In Oracle we have multiple users or schemas in the same database.

So, if what you really want is to access objects from a different schema all that has to happen is for that schema to grant you privileges. You can then reference the tables (or whatever) in your SQL.

User JOE grants you select on his table

SQL>  conn JOE/SOAP
SQL>  grant select on my_table to ABC;

You can then run queries on it:

SQL>  conn ABC/DEF
SQL>  select * from joe.my_table;

In your example you used INFORMATION_SCHEMA. The Oracle equivalent of this is the data dictionary, an enormous library of views. Find out more.

Public access is granted by default on most of them. So you can select from USER_TABLES, USER_CONSTRAINTS and USER_CONS_COLUMNS to re-create that query (assuming I have understood it correctly).

Query more than one database?

1 Answers1