Wheres my TNS connection? SHOW TNS

Lots of users have been head scratching as to which tnsnames.ora is being found and used when  connecting to the database with SQLDeveloper and with SQLcl.

In the latest release we’ve added another new command.

SHOW TNS

What this will do is walk the locations where we look for tnsnames.ora and list these out in order.  Then it will tell you which one the tool will actually use and list the entries for you.

So, with nothing set, no ORACLE_HOME, no TNS_ADMIN, here’s what you get.  TNS is going to look in your home directory for a file called tnsnames.ora or .tnsnames.

Now, if we have an ORACLE_HOME set, we’ll look for $ORACLE_HOME/network/admin/tnsnames.ora

Further, if we set TNS_ADMIN, it will override ORACLE_HOME and go to that location as shown here.

Lastly, we’ll come back to the User directory. If you have a tnsnames.ora there or a .tnsnames, it will override everything and this is what will be used.

Now, go ahead and make a connection.  You can then do another new command called

SHOW CONNECTION

which will show you how you are connected and what you are connected to.

Finally, you can get a look at which driver you are using for a connection using

SHOW JDBC

which will show something like this, detailing types, versions and the URL of the connection you have.