DBLINK : Connect to remote database

Connect to remote database

Create database link by executing following command in TOAD.

1.     create database link dblink1 connect to apps identified by apps  using 'TESTUPGRADE';

  Where ‘dblink1’ is link alias, first ‘APPS’ is schema name ,second ‘APPS’ is password and    ‘TESTUPGRADE’ is service name.

2.  select * from tablename@dblink1

   Before executing query you need to add entry of remote database in the ‘TNSNAMES.ORA’     as illustrated below using following command.

  vi /opt/d02/oracle/proddb/9.2.0/network/admin/PROD_test/tnsnames.ora

how to connect to remote database using dblink

Now you can access the tables of remote database by just adding DBLINK  alias at the end of the table e.g. tablename@dblink1. Best way to compare data of same table from two different databases.

If you want to remove the link then:

3.       drop database link dblink1 (if you want to drop link).

Your feedback/comment is quite important for improving this blog so feel free to leave your comments & suggestions