How to create Database Link without editing tnsnames.ora file

Tuesday, July 14, 2009

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.


Share/Bookmark

21 comments:

  • 4gb micro sd karte

    keep in mind on unix there's an additional directory (i think i remember /etc/tnsnames.ora on tru64, the documentation states /var/opt/oracle for sun) where this file could reside. See the full search order for tnsnames.ora at http://download-uk.oracle.com/docs/cd/B19306_01/network.102/b14212/config_concepts.htm#sthref245 .

  • Damir Vadas (aka Funky)

    Marko,

    This is something to remember....

    Never thought on such an issue in this way-cool!

    Cheers!

    Damir Vadas
    http://damir-vadas.blogspot.com

  • Very useful. Helped me a lot. Thanks.

  • Thanx.Solved my problem in a second.

  • I am getting the following error when trying to create databaselink as you mentioned format :-(



    ERROR: ORA-02010: missing host connect string


    Query = Create
    Database link Arjun connect to Scott identified by "tiger"
    using
    using

    '(DESCRIPTION=

    (ADDRESS=

    (PROTOCOL=TCP)


    (HOST=10.60.1.51)

  • Marko Sutic

    Hi Arjun,

    you made syntax error.
    Just copy text from my example and enter your connection parameters.
    It should work.

    Regards,
    Marko

  • Thanks for suggestion - solved by problem in an instance i.e. no access to change tnsnames.ora file.

  • Marko Sutic

    Glad to hear that Peter ;)

    Regards,
    Marko

  • Hallowtheme

    This really helped me out. I could not create a link using the normal methods. Sometimes for me, IP addresses seem to work better than machine names.

  • Marko Sutic

    Hallowtheme, this method is very useful when you don't have access to database server to modify tnsnames.ora file.

    It should work with IP names and with machine names. If you have problems with machine names then check your DNS (address resolution mechanism).

    Thanks for comment.

    Regards,
    Marko

  • HomeBiz4Real

    Hi Marko,

    I thought I did exactly as you stated but seems I'm still missing out something.

    It creates but select statement hangs when used.

    The two DBs are within different infrastructures that require firewall opening.

    Could this be responsible for the issue?

    Gideon

  • Marko Sutic

    Hi Gideon,

    confirm with firewall guys that port you are using is open on specified IP.

    Then execute simple statement to check dblink - "select * from dual@REMOTE_DB". If everything is OK you should receive answer immediately.

    Regards,
    Marko

  • Yoite

    Thank you very much, Marco!
    Your post was extremely helpful for us!

  • Marko,

    Thanks a lot. It was really helpful for me.

  • I'm looking to link 'my machine' db server to 'another machine's' db server, to do so how i need to give the "service name"

  • dear sir,i want to connect to another database which is not on same network.what is the method to connect another database which is on different network.
    R:Burhan Masood

  • Marko Sutic

    Hello Burhan,

    what seems to be the problem?
    Do you have connection between database servers?
    Have you tried to create database link? What error do you get?

    Regards,
    Marko

  • Thanks a lot.
    Nice and easy.

    Linked to Oracle9i database from
    Oracle10g in seconds.

  • Hari Thanneru

    hai good xplanation what is the differnce between public dblink and private dblink.

  • Thanks. This solution work perfect for me.

  • Post a Comment