How to create a relation from Oracle to another physical database
... exactly this was the question of a customer, because he wants to join tables between two Oracle databases. Well, the idea for this solution is based upon the fact, that we can have a foreign key constraint on a view. So let's go ahead:
1. We create a view which points to a table/view on the foreign database, using a database link:
create view test_view_dblink as select * from some_table@external_oracle_database;
2. We create a foreign key constraint on that view
alter view test_view_dblink add constraint test_view_dblink_fk foreign key (column_name) references table_in_local_database(column_name) disable;
The "disable" clause at the end of the statement is the important thing because constraints on views must be disabled.
2b. If we would need a primary key we also can define one:
alter view test_view_dblink add constraint test_view_dblink_pk primary key (column_name) disable;
Last but not least: with this technique, we can create relations to all databases for which we have a oracle connector/gateway (e.g. IBM DB2, ...). Check it out!