Wednesday 30 June 2010

Database links for beginners

It cannot be easier.  If you want to run SQL between database instances, why not use a database link.  Yeah, the security does seem a little loose, but the implementation is awesome.  Remember that you cannot run DML via the link, only SQL.  You can do updates, inserts and deletes – but no creates etc.

Say I have E1prod and E1dev databases.

Best thing is to create a link from e1prod to e1dev, as this is the more secure route.

login to e1prod with DBA permissions

create public database link "jde_e1dev.oracle.com"
connect to JDE
identified by password
using 'e1dev';

Then from e1prod you can do:

select count(1) from testdta.F0101@jde_e1dev ;

Note that the user name prefixes the dblink name, this is cool if you user other users.  Also all of your queries in e1dev over the link are going to use the JDE username.

You can check details of the link in select * from all_db_links;

No comments: