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

March 5, 2012 by 18 Comments

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.

Filed Under: Oracle

Comments

  1. Tim says:
    March 5, 2012 at 1:30 pm

    I tested in 11.1.0.7 and it works.

    Reply
  2. Nicolas GERARD says:
    March 5, 2012 at 3:34 pm

    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

    Reply
  3. Randolf Geist says:
    March 5, 2012 at 4:46 pm

    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

    Reply
    • Tanel Poder says:
      March 6, 2012 at 7:48 am

      Randolf, did you actually try selecting from it?

      Reply
      • Randolf Geist says:
        March 6, 2012 at 4:26 pm

        > 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

        Reply
        • Tanel Poder says:
          March 6, 2012 at 6:16 pm

          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 :)

          Reply
          • Swastik says:
            January 17, 2013 at 5:03 pm

            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:
    March 6, 2012 at 11:27 am

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

    Cheers

    Carlos.

    Reply
  5. Uwe Hesse says:
    March 11, 2012 at 7:03 am

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

    Reply
  6. Scott Petersen says:
    March 15, 2012 at 11:00 am

    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…

    Reply
    • PMcM says:
      March 30, 2012 at 4:23 am

      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

      Reply
  7. Anantha says:
    March 16, 2012 at 10:12 am

    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 @\”:/\”

    Reply
  8. BALAJI says:
    May 6, 2012 at 1:20 am

    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.

    Reply

Trackbacks

  1. Log Buffer #262, A Carnival of the Vanities for DBAs | The Pythian Blog says:
    March 9, 2012 at 1:05 am

    [...] Tanel Poder guides us about creating a database link with the new host:port/service syntax. [...]

    Reply

Speak Your Mind

*