Skip to Main Content
  • Questions
  • Using different DB Links in different environments

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Suresh.

Asked: July 07, 2022 - 11:44 am UTC

Last updated: July 07, 2022 - 2:04 pm UTC

Version: 19C

Viewed 10K+ times! This question is

You Asked

Greetings Connor

The dblinks are different in different environment, they all put them into the code with these links, every time you make changes to the object we have to be careful and change these links to make work in the other environment. We can not use dynamic sql. Only option left for me is using Synonym like below. My question is there any other easy option other than the synonym. I remember you were saying not good idea to create dblinks in synonyms. I am thinking of creating this in a procedure in all the environments using global_client and run this every time when there data refresh happens.

For SIT
create public synonym <some_table_name> for <remote_table_name>@<remote_sit_link)

For PROD
create public synonym <some_table_name> for <remote_table_name>@<remote_prod_link)

What do you think? Do you have any other better solution than this.

Thanks Connor for your time

Best Regards
Suresh

and Chris said...

I'm unclear exactly what you're asking for here - is it that you want the DB links in PROD to only point to other production databases, the DB links in test to only point to other test databases, etc.?

If so creating synonyms is one method - though I think it's better to use private synonyms only created in the users that need them.

A similar approach is to create views to "hide" the link, e.g.:

create view <some_table_name> as
  select * from <remote_table_name>@<remote_prod_link>;

create view <some_table_name> as
  select * from <remote_table_name>@<remote_sit_link>;


The challenge with this is you're changing the DDL between each environment to use a different link. This increases the chance of mistakes 0 e.g. deploying synonyms/views to prod that still use the test links.

To avoid this you can use a database link with the same name in all environments and change its definition in each database:

-- in TEST
create database link db_link 
  using 'test_db_string';
  
-- in PROD
create database link db_link 
  using 'prod_db_string';


Then all code in all environments uses

select ... from <remote_table_name>@db_link


The connect string must be a literal, so you can't bind this. This means the risk of deploying a link with the wrong definition is still possible!

Personally, I prefer using links with the same name in all environments. These change less often so there's less risk of pointing to the wrong database.

Ultimately you'll have to take care to ensure all links point to the right place. It's a good idea to put further protections in place to ensure links don't accidentally point to the wrong place. For example, ensure the firewall closes SQL*Net access between the production and test databases.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database