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: Mercadante, Thomas F \(LABOR\) <>
Date: Wed, 22 Nov 2006 09:29:13 -0500
Message-ID: <ABB9D76E187C5146AB5683F5A07336FFE088B6@EXCNYSM0A1AJ.nysemail.nyenet>


I usually create private database links in the schema owner's account. I then create views using that link and grant access to the view to various users in the database. This allows me to manage all db links in one place, and also manage access to the db links to other users. This way, I have the same db link name in all the databases and the only thing that changes is either the connect string in the db link creation (point to either staging or production) or the tnsnames file if staging and production are on different machines.

As for GLOBAL_NAMES, we have it set to FALSE but the db_domain value is declared. So all of our database links are created like:

Create database link dbname.domain_name
Connect to 'user' identified by 'password' Using 'tnsnames entry';

Hope this helps.


This transmission may contain confidential, proprietary, or privileged information which is intended solely for use by the individual or entity to whom it is addressed. If you are not the intended recipient, you are hereby notified that any disclosure, dissemination, copying or distribution of this transmission or its attachments is strictly prohibited. In addition, unauthorized access to this transmission may violate federal or State law, including the Electronic Communications Privacy Act of 1985. If you have received this transmission in error, please notify the sender immediately by return e-mail and delete the transmission and its attachments.

-----Original Message-----

[] On Behalf Of Jesse, Rich Sent: Wednesday, November 22, 2006 9: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:29:13 CST

Original text of this message