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

Submitted by tanelpoder on Mon, 2012-03-05 15:57

Share
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.

eg:MY test

SQL> create database link test11g connect to user_data2 identified by xxxxxx using ‘10.0.0.42:1521/user’;

Database link created.

SQL> select * from global_name@test11g;

GLOBAL_NAME
——————————————————————————–
USER