Database Links – When And How To Recreate Them

Database Links – When And How To Recreate Them

November 2, 2016 4 By dianarobete

A security audit has identified the password for schema APP_USER, in HRPRD database has not been changed in the past 2 years. In order to pass the security audit, the password needs to be changed as soon as possible.
This sounds very easy to accomplish, as you have the process all documented: “How To Change The Password For APP_USER in HRPRD”. You roll up your sleeves and get down to work.
Within five minutes, you re-run the security audit and send it to the auditors, with a big smile on your face.
One more thing is checked off your to do list for the day.

You take a five minute break, to get a coffee. When you’re back at your desk, you notice an email and a voice message. A user is panicked because an important report, in the financials database, is failing with a “Invalid Username/Password” error.
There were no changes made in the Financials database, what could be wrong?

As you put your troubleshooting cap on, it dawns on you, the password change in HRPRD database, affected something else as well: database links in other databases, including financials.

How is this possible? Let’s dive deeper into database links.

1.What Is Needed For Database Links To Work?
2.What Can’t You Do With Database Links?
3.When Do You Need To Recreate Database Links?
4.How To Recreate Database Links?

1.What Is Needed For Database Links To Work?

By definition,a database link is a schema object that allows you to connect to another database. Which means that 2 important things need to be specified:

  • WHERE to connect:
    – remote database
    – tns alias
    – easy connect string
  • WHO to connect as
    – current user in the database.
    – different username/password

APP_USER user in FINPRD database, owns a database link (HRPRD.DBAPARADISE) that connects to APP_USER in HRPRD database. If any of the 2 variables change: username/password or the host string, the db link will not work anymore.

In this example, if the APP_USER password changes in HRPRD, the database link from FINPRD, HRPRD.DBAPARADISE, will not work anymore.

2.What Can’t You Do With Database Links


  • pre-qualify with the owner when referencing db links.
    ie. drop database link app_user.hrprd.dbaparadise — this will not work.
  • test db link in different schemas.
    ie. select * from dual@app_user.hrprd.dbaparadise –this will not work

3.When Do You Need To Recreate Database Links?

There are two reasons you would recreate a database link:

  • If anything about the connecting user has changed: new password, or new username/password
  • If anything about the connection string has changed: the tns alias is no longer valid, or if you want to point the database link to a different database.

4. How To Recreate Database Links?

One of the features of Oracle database I love, is the ability to “create or replace” an object. I love this feature, because, I need not worry about losing permissions, when I recreate an object (where applicable of course).
I’ve got some bad news for database links.

You cannot “create or replace” a db link. In order to recreate a database link, first, it needs to be dropped, then created again.

Because “A Good DBA Always Has A Rollback Plan” , here is the DBAParadise Approved Approach of recreating database links:

1. Backup the existing database link.

Never ever drop a database link, without extracting the DDL for it. Why? In case you need to rollback. It takes 5 seconds to extract the database link, and have you covered, plus it helps with the syntax for creating the new link.

DDL Alert! Starting with and going forward, for security reasons, dbms_metadata.get_ddl will not return the encrypted password used in db link.
If you need to backup the database link with the encrypted password, export them using datapump expdp with the option: INCLUDE=DB_LINK.

?View Code ENGLISH
Script name: get_db_link_ddl.sql
Example:     @get_db_link_ddl.sql OWNER DB_LINK_NAME
Created By   Diana Robete
Copyright:   @2016
   set define '&'
   define oname=&1
   define dname=&2
   col line for A100 word_wrapped
   exec dbms_output.enable(2000000);
   exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',TRUE);
   exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'PRETTY',true);
   select dbms_metadata.get_ddl('DB_LINK',db_link,owner) line
   from DBA_DB_LINKS
   where upper(owner)=upper('&oname')
   and upper(db_link_name)=upper('&dname');

2. Connect as the database link owner

In order to drop the db link, you must be connected as the owner of the link. This can be accomplished in two ways:

  • connect as the owner of the db link
    connect db_link_owner/password
  • proxy as the db link owner.
    Don’t know what a proxy user is, or how to set it up? Look no further, check out last week’s article here.

    connect your_user[db_link_owner]/your_password

3. Test the database link

To confirm the status of the link prior to dropping, test it! This either works, or it doesn’t. And now you know it!

 select 'DB Link is working!' || sysdate from dual@db_link_name;

4. Drop the database link

drop database link DB_LINK_NAME;

5. Create the database link

   create database link DB_LINK_NAME
   connect USER identified by PASSWORD
   using 'connect_string';

6. Test again the database link
This is important, and many times it gets forgotten: Test the new db link!!!

 select 'DB Link is working!' || sysdate from dual@db_link_name; 

If you enjoyed this article, and would like to learn more about databases, please sign up below, and you will receive
The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!