RE: Advice for dblinks between two prod DBs.
- From: "Jesse, Rich" <Rich.Jesse@xxxxxx>
- To: "Hemant K Chitale" <hkchital@xxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 22 Nov 2006 08:55:09 -0600
Excellent! The one stickler with GLOBAL_NAMES is that I'm planning on
using one or more standby databases (i.e. Data Guard) and I haven't
researched yet wether it's needed. I have created a physical standby
with GLOBAL_NAMES set to false, seemingly without a problem, but I want
to make sure.
Thanks, Hemant!
Rich
-----Original Message-----
From: Hemant K Chitale [mailto:hkchital@xxxxxxxxxxxxxx]
Sent: Wednesday, November 22, 2006 8:49 AM
To: Jesse, Rich; oracle-l@xxxxxxxxxxxxx
Subject: RE: Advice for dblinks between two prod DBs.
No, even in 9i, you cannot create Database Links in another schema
as the " <schemaname>." actually becomes part of the Link name
in the creator's schema --- as you have noticed.
What I do is {as I do not have the other schema's password -- I
am not supposed to have it } is to use EXECUTE IMMEDIATE
grant create database link to scott;
create or replace procedure scott.tmpcrtdblink22nov06
as
begin
execute immediate 'create database link remote_db connect to
remote_user identified by remote_password using ''tns_connect_string'' '
;
end;
/
execute scott.tmpcrtdblink22nov06;
drop procedure scott.tmpcrtdblink22nov06;
revoke create database link from scott;
select db_link, username, host from dba_db_links where owner = 'SCOTT';
The remote_user is NOT the owner of the base tables in the remote_db
but another "shadow" account with SELECT only privileges on the base
tables.
Similarly, if other users locally need to access the remote database ,
they have their own dblinks --- particularly so when SCOTT and TOM
are accessing different tables and/or with different privileges in
remote_db
(thus, using different "shadow" accounts in the remote_db as well).
Since I really don't have database accounts for end-users but only
accounts for applications / application schemas, dblinks are required
only between applications and so it becomes easy to come up with
meaningful names for the "shadow" account {use a name which represents
which application in which database will use this account} and the
dblink name {use a name which represents which database and which
application/schema/shadow the dblink connects to}.
GLOBAL_NAMES, I leave as FALSE. TRUE causes problems whenever
I clone databases and forget to change the DBNAME/GLOBAL_NAME etc.
We really don't need it -- it is only mandatory for Advanced Replication
with Multi Master implementations {because it the database name / link
name
becomes part of the global_trans_id and so has to be unique}.
Hemant
At 10:16 PM Wednesday, Jesse, Rich wrote:
>While testing, I think I've hit my first hurdle. As of 9i, a DBA can
>.......................
> Is there any way to do this without granting CREATE
>DATABASE LINK to the owner?
>.....................
>I'm also struggling with GLOBAL_NAMES true/false. ....
Hemant K Chitale
http://web.singnet.com.sg/~hkchital
--
//www.freelists.org/webpage/oracle-l
Other related posts:
