3 Replies Latest reply: Mar 11, 2014 2:12 AM by smit_sign-Oracle RSS

    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 emp@DBS1@DB1

      union

      select * from emp@DBS2@DB2

      union

      select * from emp@DBS3@DB3

      union

      select * from emp@DBS4@DB4

      union

      select * from emp@DBS5@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

        • 1. Re: Accessing Table from Multiple databases
          Emad Al-Mousa

          how about the option of "creating database links" ?

           

          Oracle 11g DBA: Database Links

          • 2. Re: Accessing Table from Multiple databases
            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

            • 3. Re: Accessing Table from Multiple databases
              smit_sign-Oracle

              Hi

               

              You may opt for DB link

               

              select * from <schema_name>.emp@DB_LINK1

              union

              select * from <schema_name>.emp@DB_LINK2

              union

              select * from <schema_name>.emp@DB_LINK3

              union

              select * from <schema_name>.emp@DB_LINK4

              union

              select * from <schema_name>.emp@DB_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