1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Re-create database links for test environment

Discussion in 'SQL PL/SQL' started by Razvan Drumea, Jun 16, 2013.

  1. Hello,

    I need a script that can recreate the database links after a refresh on the test environment. For you to understand me better I will explain all the proedure :) :

    1) The database backup is moved from production environment to test environment and a new instance of database is started using this backup.
    2) There are a lot of db users that use links from a db to another and it's takeing a long time to drop the database links that reach the production environment and after that redo the db links to reach the tnsnames of test environment.

    I found some scripts that help me to drop them all by creating a procedure under SYS schema and execute it for each user but it doesn't help because I have to re-create some db links back.

    Thanks a lot for your help an suggestions!
    Razvan Drumea
  2. bkoniki

    bkoniki Active Member

    Likes Received:
    Trophy Points:

    It is unusual in real situation to restore production back up for test environment. Generally production is only restored to another location, due to disk failure etc, but in that case it becomes production environment again.

    Most likely way of refreshing test environment with production environment is to export schema level who owns the database. This is the same schema used
    during creation of original tables.

    Schema is restored in test without dropping the schema in test. Data links are not generally exported in the first place. When the schema is imported, existing database links are not destroyed either.

    Database links generally owned by schema user and not by SYS user.
  3. zargon

    zargon Community Moderator Forum Guru

    Likes Received:
    Trophy Points:
    Aurora, CO
    I have seen production cloned to test and dev numerous times, for a number of clients, so it isn't unusual. Simply because you don't do it doesn't mean others follow that same practice.

    Also due to auditing requirements database links may not be permitted in a production database, especially to non-production systems. The simple way to do this is to create a script that creates these db links BEFORE the production data replaces the current test/dev data. You then have a script to run after each refresh that creates all of the required db links.