Accessing Table from Multiple databases

Basavaraj - Oracle

Hi Team,

We have a situtation where we need to access the same table across 5 database - each have one common schema with union output.

Example :

Database are and schema is as below,

DB1 - > DBS1 Schema -> EMP Table

DB2 -> DBS2 Scehma -> EMP Table

DB3 -> DBS3 Schema -> EMP Table

DB4 -> DBS4 Schema -> EMP Table

DB5 -> DBS5 Schema -> EMP Table

Output requested on Seperate DB.

select * from [email protected]@DB1

union

select * from [email protected]@DB2

union

select * from [email protected]@DB3

union

select * from [email protected]@DB4

union

select * from [email protected]@DB5

CAN YOU PLEASE SUGGEST WHICH WILL BE THE BEST WAY TO ACHIVE THIS. Materilized View or normal VIEW or is there any other metod to achive this.

Regards,

Basavaraj M

Emad Al-Mousa

how about the option of "creating database links" ?

Oracle 11g DBA: Database Links

Ananthram

Hi Basavaraju,

I presume that you are aware of what db links are and I suggest that you create db links. Here are few more tips:

Depending on when you will be accessing these tables, you may choose to go for either a normal view or a materialized view.

You do not need to 'refresh' a regular view, you can directly go ahead and run the view whenever needed, but that may be a hit on the performance.

With a Materialized view, you will not be making remote database calls when you access this query-assuming that you create & access this Materialized view in one database. But you may then need to refresh this view in a timely fashion-the frequency depends on your application needs(hourly/daily/weekly etc).

The up side of materialized view is that you can have the query results faster.

Hope I have provided enough information for you to decide.

BR,

Ananthram

smit_sign-Oracle

Hi

You may opt for DB link

select * from <schema_name>[email protected]_LINK1

union

select * from <schema_name>[email protected]_LINK2

union

select * from <schema_name>[email protected]_LINK3

union

select * from <schema_name>[email protected]_LINK4

union

select * from <schema_name>[email protected]_LINK5

>>CAN YOU PLEASE SUGGEST WHICH WILL BE THE BEST WAY TO ACHIVE THIS. Materilized View or normal VIEW or is there any other metod to achive this.

As far as there is not much of updates /inserts performed on the tables I would suggest you to go with Mview for faster results.

Smit