How to create Database Link without editing tnsnames.ora file

A month ago one of our developers needed access to some objects from one schema to another using database link. To enable database link he tried to create entry in tnsnames.ora file but had a problem with insufficient permissions. As a developer he has limited privileges on Unix machines so he can't edit and save tnsnames.ora file.

But there is solution for this little problem.
You can create functional database link without editing tnsnames.ora file.



Little demo case:

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:

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:

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.

Tweet