Create Database Link using EZConnect Syntax

EZConnect syntax eliminates the need for service name lookup in tnsnames.ora files when connecting to an Oracle Database across a TCP/IP network. In a previous post we have already demonstrated how to use this syntax to establish connection in SQL*Plus.

Now, we are going even further. You can use the same syntax for creating Database Links (assuming that you have a privilege to create database links in general).

CREATE DATABASE LINK dbl_test
CONNECT TO scott
IDENTIFIED BY tiger
USING ‘scott/tiger@192.168.1.180:1521/ORCL’;

Database link created.

See http://www.orafaq.com/wiki/EZCONNECT for more information on the syntax and prerequisites.

My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Advertisements

Use EZConnect Syntax for Oracle connection string when you cannot change the TNS Names file.

In some companies, there are security regulations that disallow users to change their local TNS Names file, so if there is a need to connect to a host that is not already defined in the TNS Names file, a different approach has to be employed.

There are a couple of ways to work around the above mentioned limitation:

Note: To get to a SQL*Plus command prompt without connecting use “/nolog” attribute with sqlplus:

C:\>sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Fri May 20 14:22:25 2016
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
SQL>

1) TNS-less connection string:

SQL> conn scott/tiger@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.180)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORCL)))
Connected.

2) EZConnect connection string:

SQL> conn scott/tiger@192.168.1.180:1521/ORCL
Connected.

See http://www.orafaq.com/wiki/EZCONNECT for more information on the syntax and prerequisites.

I was able to use both these approaches with SQL*Plus and SQL Loader.

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.