Re: DBLINKs in critical production system

On 5/1/07, Nigel Thomas <nigel_cl_thomas_at_yahoo.com> 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