Create a database link with the new host:port/service syntax

I just noticed that (finally) in 11.2 this syntax is supported:

SQL> CREATE DATABASE LINK demo_x2 
  2  CONNECT TO tanel IDENTIFIED BY password 
  3  USING 'exadb03:1521/DEMO';
Database link created.

This just makes life a bit easier as there’s no need to use the long TNS format entry (or a tnsnames.ora/LDAP alias). It might work in 11.1 too (haven’t tested) but it didn’t work on 10.2.0.4 …

Update: This feature works for dblinks in 10.2 onwards – when I tested it on my 10.2, I got an error initially, but it was because the hostname I used didn’t resolve to an IP. Thanks to Randolf Geist for pointing this out.

In case you didn’t know, the sqlplus supports such an easy connect method since 10g:

tanel@mac02:~$ sqlplus tanel/password@exadb03/DEMO
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Mar 5 09:51:27 2012
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL>

If you omit the port from the syntax, it defaults to 1521.

This entry was posted in Oracle. Bookmark the permalink.

17 Responses to Create a database link with the new host:port/service syntax

  1. Tim says:

    I tested in 11.1.0.7 and it works.

  2. And if you omit the service name, it defaults to the DEFAULT_SERVICE_listener_name parameter van de listener.
    http://docs.oracle.com/cd/E11882_01/network.112/e10835/listener.htm#BGBJJBCF

    Thanks for sharing.
    Cheers
    Nico

  3. Hi Tanel,

    I’m pretty sure that the easy connect syntax “//host:port/service” was already supported for DB links in 10.2. I’ve just tried it on my 10.2.0.4 instance and it worked like a charm…

    Cheers,
    Randolf

    • Tanel Poder says:

      Randolf, did you actually try selecting from it?

      • > Randolf, did you actually try selecting from it?
        Yepp, as I said works like a charm. I’ve seen it at various clients already using it in 10.2 databases, so I was quite sure that it should work in 10.2, too.

        Doesn’t work for you?

        Randolf

        • Tanel Poder says:

          Cool – I just retested on 10.2 and yeah it works! I think I missed the fact that the hostname where I pointed the dblink to (from my VM with Oracle 10.2) wasn’t resolvable and thus returned this error:

          SQL> SELECT * FROM dual@d;
          SELECT * FROM dual@d
                             *
          ERROR at line 1:
          ORA-12154: TNS:could not resolve the connect identifier specified
          
          

          But yeah it was because hostname resolving error, not that 10.2 didn’t support it!

          Thanks for setting things straight :)

          • Swastik says:

            What do you mean by “the hostname where I pointed the dblink to (from my VM with Oracle 10.2) wasn’t resolvable”? I was trying to connect my database to another database outside my network and I was getting the same error: “ORA-12154: TNS:could not resolve the connect identifier specified”

            After adding an entry of the target database in the TNSNAMES.ORA file, the error that now comes is: “ORA-12170: TNS:Connect timeout occurred”

            I have already asked the networks team to open up the firewall for port 1521 for the target database’s IP. Do I need to open any other port?

  4. CarlosAL says:

    Isn’t it called ‘EZConnect syntax’ by the way?

    Cheers

    Carlos.

  5. Uwe Hesse says:

    Although not entirely a New Features, it is nice to see postings about things in Oracle that work well :-)

  6. Scott Petersen says:

    Another thing to consider is that the sqlnet.ora in the ORACLE_HOME/network/admin of the database must contain EZCONNECT in the NAMES.DIRECTORY_PATH entry. This is the same as if you want to use ezconnect syntax in the LOCAL_LISTENER and REMOTE_LISTENER parameters.

    No need to ask me how I know…

    • PMcM says:

      Just an update to this comment

      If NAMES.DIRECTORY_PATH is not specified in SQLNET.ORA then the default options will allow EZCONNECT to work via SQL*Plus or the DB Link

      If NAMES.DIRECTORY_PATH in a SQLNET.ORA is specified then EZCONNECT must be included in the list of allowed options for this connection method to work

      Learnt the hard way after trying to compare 2 servers where EZCONNECT worked on one server but not the other

  7. Anantha says:

    There’s one nuance we’ve to deal with when not specifying password on the command line. In such a case you’ve to specify the connect string as follows @\”:/\”

  8. BALAJI says:

    Hi,
    This ‘EZConnect syntax’ is supported for DB links in 10.2.0.1.0 database version. I’ve just tried it on my 10.2.0.1.0 instance and it worked.
    Its easy to create db links and connect to database using EZConnect syntax.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>