2011/06/30

Create db link without editting tnsnames.ora

Often database link is the best way to transport rows, current values of sequences or to know differences between rows in tables in remote databases: developing, test, production, etc

To create db link you need a 'create database link' privilege. As a database developer, i always ask DBA to grant this privilege when he create my developing schemas (with connect, resource and create view privileges).

When i want to access remote database with db link i need to add it's description to a tnsnames.ora file. As a developer i have to ask DBA to do it: i don't have an access to tnsnames.ora in database server. Sometimes it can takes hours.

When i plan to create db link, run some query and drop it, i create it this way:
SQL> CREATE DATABASE LINK xe_myrudw
  2    CONNECT TO hr IDENTIFIED BY hr USING '
  3    (DESCRIPTION =
  4      (ADDRESS = (PROTOCOL = TCP)(HOST = myrudw-ccfz91j.r2.money.ge.com)(PORT = 1521))
  5      (CONNECT_DATA =
  6        (SERVER = DEDICATED)
  7        (SERVICE_NAME = XE)
  8      )
  9    )
 10  ';

Database link created.

SQL> SELECT 1 a FROM DUAL@XE_MYRUDW;

         A
----------
         1

without adding connect string to tnsnames.ora.

No comments:

Post a Comment