Forum Stats

  • 3,873,041 Users
  • 2,266,499 Discussions
  • 7,911,412 Comments

Discussions

Categories

Accessing Table from Multiple databases

Basavaraj - Oracle
Basavaraj - Oracle Member Posts: 2 Employee
edited Mar 16, 2014 1:09PM in General Database Discussions

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

Tagged:
· Share on TwitterShare on Facebook

Answers

  • Emad Al-Mousa
    Emad Al-Mousa Member Posts: 716 Bronze Trophy

    how about the option of "creating database links" ?

    Oracle 11g DBA: Database Links

    · Share on TwitterShare on Facebook
  • Ananthram
    Ananthram Member Posts: 19
    edited Mar 10, 2014 10:55PM

    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

    · Share on TwitterShare on Facebook
  • smit_sign-Oracle
    smit_sign-Oracle Member Posts: 59

    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

    · Share on TwitterShare on Facebook
This discussion has been closed.