4

We are writing some interfacing routines in PL/SQL to transfer data between several oracle database by using another oracle database as host. (ie hr -> host -> finance)

the transfers are happening over db_links essentially

insert into schema.tablname@dblink1 select * from schema.tablename@dblink2;

(its more complicated then that with multiple tables and transformations etc.. but that's the general idea)

the discussion we have been having here is which of the following should do

  1. reference "schema.tablename@dblink" everywhere in out code

  2. create synonyms (public or private) "create synonym tablename for schema.tablename@dblink"

  3. create views on the object " create view tablename as select * from schema.tablename@dblink"

are there any other options? are any inherently better then the others?

NB:the dblink names are standardised throughout each level dev/test/prod so that dblink 'server1' goes to the dev server on the dev host and the test server on test host etc..

none of the table names should ever exist on multiple servers

ShoeLace
  • 3,476
  • 2
  • 30
  • 44

1 Answers1

3

Location transparency is easiest setup by creating synonyms for your remote objects. That is easier to maintain than having the remote addresses in every SQL. How would you make a quick test for something in an other remote database? Just re creating the involved database links is enough to accomplish that.

An other option could be to create snapshots are materialized views from the remote tables in the local database but that also requires a database link. It would have a good performance at the cost of extra space.

  • i dont think we are worried about the performance right now.. we dont expect it to be an issue.. but that is an idea for some of teh tables at least. – ShoeLace Mar 05 '14 at 15:00