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