Can a table be shared across multiple databases

Question

I know that this is most probably not possible. But I will still detail my problem here, and if anyone has something similar to what I need, that would be great.

I have a file validation system which is hosted on a Windows server. This system holds a metadata table which is used by the front-end file validation application to validate various types of data. Right now it caters to a single application which is hosted on the same database as the metadata table.

The problem is that I want this system to be scalable so that it can validate files for a variety of applications some of which exist on different databases. Since some of the checks in my metadata table are based on Pl/SQL, I cannot run these checks unless the database for both the file validation system and the application database is the same. Is there a way for a table to be shared across multiple databases at once? If not, what could be the possible workarounds for this?

Answer 1

If you want to access several tables on several databases as if it was one table then you have to use a view which uses database links. You should first learn about database links and then creation of the view should not be the problem.

A database link allows you to access another database table just as a table on the same database. All you do is adding the @mydblink to the table name once you created the db link. Example

CREATE DATABASE LINK mydblink 
CONNECT TO user IDENTIFIED BY password
USING 'name_of_other_db'

SELECT * FROM sometable@mydblink

It works well and there even is a two phase commit for updates in the remote and local database. There is some more to know about it depending on your set-up but you can read all about it in the oracle documentation. I have worked in a larger project extensively with database links and that is the right approach for what you want to do.

http://docs.oracle.com/cd/E11882_01/server.112/e25494/ds_concepts002.htm#ADMIN12083

http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_5005.htm

Here is a link that I found after some googling telling how to build a view that accesses data at several databases and also gives some information about the expected performance

http://www.dba-oracle.com/t_sql_dblink_performance.htm