0

Is it possible to have dynamic database links in Oracle? I am using Oracle9i database.

Let me illustrate by what I require. I have several views that are formed using dblinks. For example:

CREATE VIEW MYVIEW AS SELECT * FROM TABLE@MYDBLINK.WORLD

mydbLINK.WORLD NEEDS TO POINT TO DIFFERENT INSTANCES OF THE DATABASE. SO AT ONE POINT IT MUST POINT TO THE DATABASESERVER1 AND THEN IT MUST SWITCH TO DATABASESERVER2.

Currently we are doing this by dropping the link and recreating it. When we do this, we have to recompile all the objects, refresh all the snapshots. Is it possible to avoid the dropping and recreating? I understand as soon as teh view changes, I will have to recompile the objects and refresh the snapshots regardless.

Has anyone implemented this feature?

abhi
  • 3,082
  • 6
  • 47
  • 73

1 Answers1

0

Try using SYNONYMS as suggested by "Justin Cave" in this post.

Community
  • 1
  • 1
Pranav Shah
  • 3,233
  • 3
  • 30
  • 47
  • Pranav, Snyonmys will not help me, since I will need a different SYNONYM for each link. In this case, my code will have to change. – abhi Jun 10 '11 at 12:58
  • The idea would be to create one synonym per table. When you start the procedure you change the synonym to point to the apropriate link, thus re-building the sysnonym. So you end up with only one synonym (per table) no matter the number of links. Even if you have 50 links, you will have only one synonym – Pranav Shah Jun 10 '11 at 21:22