RE: Database Links standards

[Mercadante, Thomas F]

Title: Database Links standards

Jeff,

 

Looks ok, except that I don't think you need step b) - the PUBLIC SYNONYM for the remote table.

I always create a private synonym within my DBA account (which happens to own the tables in the local database) and then create the VIEW and a PUBLIC SYNONYM on the view to hide everything from the user.  The view will say :

 

select * from table@dblink.

 

The developers then get access to the view only - never to the base db-linked tables.

 

Your approaches works for me, though.  Tell the developers to use it as delivered and to like it!  :)

 

hope this helps.

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
From: Thomas Jeff [mailto:[EMAIL PROTECTED]]
Sent: Friday, July 06, 2001 4:51 PM
To: Multiple recipients of list ORACLE-L
Subject: Database Links standards

We came up with the below standards with respect to  database links (heavily
used in our environment).   The result has been a billion complaints by
our developers, stating that the standards are unnecessarily complex.  I'm
curious as to what others might think, if they *are* indeed too complex.
Also what kind of naming/adminstrative standards that other shops employ.

1. To access remote data across a DB LINK, the
   standard implementation consists of four pieces:

a) A private database link owned by the schema owner of the table
   objects being accessed.  The CONNECT TO and IDENTIFIED BY clauses are
   required  
   - Naming Standard:  {remote schema}_{database name}

b) A standard PUBLIC SYNONYM created for the remote table being
   accessed across the link.
   - Naming Standard: {remote schema}_{remote table}.  The purpose of this
     synonym is both to allow portability and also to provide documentation
     of the remote connection.

c) A VIEW created as a SELECT * from the PUBLIC SYNONYM above (1.b).   
   - Naming Standard: {remote table}_VW

d) A PUBLIC SYNONYM on the VIEW above(1.c.)
   - Naming Standard: {remote table}

Thanks,
Jeff T
[EMAIL PROTECTED]