Database Link creation without editing tnsnames.ora file

u can create functional database link without editing tnsnames.ora file.



Little demo case:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

system@TEST11> select * from v$version;   BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production PL/SQL Release 11.1.0.7.0 - Production CORE    11.1.0.7.0      Production TNS for Linux: Version 11.1.0.7.0 - Production NLSRTL Version 11.1.0.7.0 - Production   5 rows selected.     system@TEST11> select * from dba_db_links;   1. no rows selected

Create database link testlink_db2 using full tns entry:

1 2 3 4 5 6 7 8 9 10 11 12 13

system@TEST11> create database link testlink_db2 2  connect to system identified by oracle 3  using 4  '(DESCRIPTION= 5    (ADDRESS= 6     (PROTOCOL=TCP) 7     (HOST=10.2.10.18) 8     (PORT=1525)) 9    (CONNECT_DATA= 10     (SID=test10)))' 11  /   Database link created.

Now little check and cleanout:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

system@TEST11> select * from v$version@testlink_db2;   BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod PL/SQL Release 10.2.0.4.0 - Production CORE    10.2.0.4.0      Production TNS for Linux: Version 10.2.0.4.0 - Production NLSRTL Version 10.2.0.4.0 - Production   5 rows selected.   -- cleanout system@TEST11> drop database link testlink_db2;   Database link dropped.

In this test case I've used system user but this also works with any user.



From documentation:
http://download.oracle.com/docs/html/B13951_01/net.htm#i1153728

server_name = (DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(PORT=port_number)
(HOST=host_name)
)
(CONNECT_DATA=(SERVICE_NAME=service_name)
)
)

where:

server_name is the name of an Oracle server that matches an entry in the RDB directory. An entry in the RDB directory can be added using the ADDRDBDIRE command.

TCP is the TCP protocol used for TCP/IP connections.

port_number is the port number of the Oracle Net listener. This is usually port number 1521.

host_name is the name that defines the system where the target Oracle server resides. This name must be in the local host definition on the AS/400 or in a name server on your network. The host name can also be entered as an IP address, for example, 161.14.10.12.

service_name is the service name of the Oracle server.