FreeLists / oracle-l / Re: DBLINKs in critical production system

["Alberto Dell'Era" <alberto.dellera@xxxxxxxxx>]

On 5/1/07, Nigel Thomas <nigel_cl_thomas@xxxxxxxxx> wrote:
(snip)
> Also be aware that (as mentioned recently on this list, see thread here:
> http://www.freelists.org/archives/oracle-l/04-2007/msg00048.html)
> any remote query (yes, SQL query - not just DML) starts a transaction on the
> remote database; some resources are not freed until the transaction is
> committed or rolled back.
Well said - in fact this is the only "big" caveat one has to remember
when using db-links, that is, it is necessary to end the transaction
even when only SELECTing data (with a rollback probably), which is
not immediately obvious. I forgot it some years ago, thus "locking" a rollback
segment which started to grow endlessly - and I got a call from the
production DBAs ;)

> Finally: in spite of all this, DB links can be a very useful part of the
> Oracle developer's toolkit. Just don't use a hammer when you really need a
> wrench.
Absolutely, in fact they are the BEST tool to use when one needs to transfer
data, or access data, from a remote db into the local, if one remembers
the aforementioned caveat.

If you need to access the remote db, of course you will need an account on it,
and a password (or other authentication credentials, let's stick with
the password).
Which is the best way - having the password stored (even in clear text pre-10g)
in a db, where only DBAs can get to it, or somewhere (in a property file,
a shell script, whatever) on some "unknown" machine in your datacenter ?

In our shop, all clients (applications or remote instances via db-link)
connect to an empty schema whose user has only the appropriate
privileges to access the objects. So, db-links are not different from
other clients ... what's the difference security-wise ?

And so on ... we use them extensively, and I have yet to find any issue.
Performant, secure, no impedence mismatch for data, etc, they are
the FIRST choice for inter-database data transfer.

--
Alberto Dell'Era
"dulce bellum inexpertis"
--
http://www.freelists.org/webpage/oracle-l