Skip to content
Home » Oracle » SQLPlus Connect without Tnsnames

SQLPlus Connect without Tnsnames

SQLPlus without Tnsnames

We usually connect a database by sqlplus through tnsnames.ora, but sometimes we are not allowable to change or access tnsnames.ora, even worse, we cannot find the location of tnsnames.ora.

So how can we connect to a new or moved database by sqlplus without using tnsnames.ora?

In this post, I provided some ways to sqlplus connect to databases without touching the original tnsnames.ora.

  1. Use Easy Connect Method
  2. Use Full Connect Descriptor
  3. Use Your Own tnsnames.ora

Use Easy Connect Method

By default, you can use Easy Connect naming method to connect databases, this is because the default connect methods of NAMES.DIRECTORY_PATH in sqlnet.ora includes EZCONNECT. Which is:

NAMES.DIRECTORY_PATH = (TNSNAMES, LDAP, EZCONNECT)

You have to provide the following information in order to use Easy Connect naming method.

  • Where do you want to go?
  • What is the listener port?
  • What is the service name?

In this case, the destination is 10.10.10.10, the listener port is 1521 and the service name is ORCL.

C:\Users\scott>sqlplus /nolog
...
SQL> conn hr/[email protected]:1521/orcl
Connected.

It seems a simple way to connect to database, you can find out more concepts and examples about Easy Connect naming method in Oracle documentation.

On the other way, if DBA explicitly disabled the naming method by removing EZCONNECT from the list in sqlnet.ora, like the following:

NAMES.DIRECTORY_PATH = (TNSNAMES)

You are no longer to use Easy Connect.

C:\Users\scott>sqlplus /nolog
...
SQL> conn hr/[email protected]:1521/orcl
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Use Full Connect Descriptor

Intrinsically, tnsnames.ora is an alias management of service names in a file. Every entry in the file contains a pair of connect identifier and connect descriptor. For example:

...
ORA11G =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ora11g-1.example.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )

In the above entry, ORA11G is the connect identifier, everything behind the equal sign is the connect descriptor. More specifically, the connect identifier is just an alias which can be used in connection string to represent their own lengthy connect descriptor for convenience.

C:\Users\scott>sqlplus /nolog
..
SQL> conn hr/hr@ora11g
Connected.

In other words, the connect identifier, the alias will be translated into its own connect descriptor at connect-time. That is to say, the real working thing is the connect descriptor. That's why we replace the connect identifier with the connect descriptor to connect a new or moved database.

SQL> conn hr/hr@"(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.10)(PORT = 1521)))(CONNECT_DATA = (SERVICE_NAME = ORCL)))"
Connected.

For safety, we use double quotes to wrap the string. It connected successfully.

Use Your Own tnsnames.ora

I know you cannot touch the original tnsnames.ora, but you can use your own tnsnames.ora. The trick is to set a TNS_ADMIN environment variable temporarily for your session and put your own tnsnames.ora in that directory. Then connection tools including sqlplus will know where to find the new tnsnames.ora.

For Linux Platform

Set TNS_ADMIN Path
[oracle@test ~]$ export TNS_ADMIN=/path
[oracle@test ~]$ echo $TNS_ADMIN
/path

This setting will override the original path to tnsnames.ora.

Modify tnsnames.ora File
[oracle@test ~]$ vi /path/tnsnames.ora
...
ORA11G_NEW =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.10)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )
Test New Connect Identifier
SQL> conn hr/hr@ora11g_new
Connected.

For Windows Platform

Set TNS_ADMIN Path
C:\Users\scott>set TNS_ADMIN=d:\
C:\Users\scott>echo %TNS_ADMIN%
d:\

This setting will override the original path to tnsnames.ora.

Modify tnsnames.ora File
C:\Users\scott>notepad d:\tnsnames.ora
...
ORA11G_NEW =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.10)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )
Test Old Connect Identifier
C:\Users\scott>sqlplus /nolog
...
SQL> conn hr/hr@ora11g
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Of course it got error, because we point the network configuration to the new location TNS_ADMIN.

Test New Connect Identifier
SQL> conn hr/hr@ora11g_new
Connected.

The trick is successfully.

Please note that, TNS_ADMIN set here is just only for your session, once you exit, the environment variable is reset. Of course, you can also set TNS_ADMIN permanently if you have the right to do it.

Leave a Reply

Your email address will not be published. Required fields are marked *