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 connect to system identified by oracle using '(DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=10.2.10.18) (PORT=1525)) (CONNECT_DATA= (SID=test10)))' / 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.
From documentation:
http://download.oracle.com/docs/html/B13951_01/net.htm#i1153728
http:https://docs.oracle.com/cd/E18283_01/server.112/e17118/statements_5005.htm
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.