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.

Advertisements

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s