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 126.96.36.199.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)))
2) EZConnect connection string:
SQL> conn firstname.lastname@example.org:1521/ORCL
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.