Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Advice for dblinks between two prod DBs.

RE: Advice for dblinks between two prod DBs.

From: Guerra, Abraham J <>
Date: Wed, 22 Nov 2006 08:22:08 -0600
Message-ID: <>

Hello Rich,

Try running this before you drop and recreate your database link:

update sys.props$
set value$ = 'DBNAME_YOU_CLONE_FROM'
  where name = 'GLOBAL_DB_NAME';


drop database link....;

update sys.props$
set value$ = 'NEW_DBNAME'
  where name = 'GLOBAL_DB_NAME';


create database link ....;

Hope it helps,


-----Original Message-----
[] On Behalf Of Jesse, Rich Sent: Wednesday, November 22, 2006 8:17 AM To:
Subject: RE: Advice for dblinks between two prod DBs.

While testing, I think I've hit my first hurdle. As of 9i, a DBA can create objects for another user, even if that user does not have privs to (e.g. CREATE TABLE scott.dba_table...). But due to the syntax, I can't seem to be able to do that with a dblink. "CREATE DATABASE LINK scott.mylink..." creates a dblink called "scott.mylink" in the current (DBA) schema. Is there any way to do this without granting CREATE DATABASE LINK to the owner? I really don't want the app owners creating their own dblinks. The next thing that'll happen is a link from test to prod because the data's newer there.

I'm also struggling with GLOBAL_NAMES true/false. If it's needed to be true (and I'm still investigating under what circumstances, if any, that needs to be "true") then what do I do when that DB is cloned from prod to test? Drop/recreate the link and make sure that packages are coded to not use the link name explicitly? Fudge the TNSNAMES.ORA in the test ORACLE_HOME (not my first choice for hopefully obvious reasons)?

Bleah. If I could just manage dblinks like other objects from a DBA account, I think most of my problems would go away...



-----Original Message-----
From: Jesse, Rich
Sent: Tuesday, November 21, 2006 11:23 AM To:
Subject: Advice for dblinks between two prod DBs.

Hey all,

I've been reading through the Distributed Database Concepts part of the 10gR2 Admin Guide to get a better understanding of how to setup dblink(s), which I've avoided since 7.4 (8.0?) when I first messed with them. After a developer inquired about using dblinks for our new ERP, I'm thinking I have a need for them now. Here's my scenario:

I have a million questions about distributed query and transaction performance, but first the setup. I want this to be usable, but flexible. My knee jerk says to create two dblinks for each app account -- one for queries, the other for DML. My main reason for this is security -- each dblink's remote account would need to be explicitly granted access to the specific ERP API views/packages needed. Is this overboard? One dblink for each app? Or one public dblink for all app accounts? <shudder>

I'm also looking for pointers to dblink hints and gotchas, like if I find an icky query running on the remote DB, how do I trace it back to the other DB (and, therefore, to the client)?. My search terms seem to be too generic to turn up anything of much use.


Received on Wed Nov 22 2006 - 08:22:08 CST

Original text of this message