Where to find tnsnames.ora in SQL Developer?

Question

I need to find the tnsnames.ora file for my connections. My servers are hosted remotely i.e. I dont have it installed on my physcial system.

Solution

SQL Developer looks for tnsnames.ora files in the following locations:

• your USER Home directory
• $ORACLE_HOME\network\admin -- assumes you have a client installed
• OS Environment Variable value for TNS_ADMIN
• Registry entry for TNS_ADMIN
• The location you specified in the preferences

The last one will trump the others.

You can see this for yourself in a SQL Worksheet, by running the command

show tns

You do not get a tnsnames.ora file 'for free.' Someone will need to build, maintain, and distribute one for and to you.

You can however connect to an Oracle Database without a TNSNames.ora file. Assuming you have all the information required (what's normally defined in the .ora file), you can make a 'Basic' connection.

Another quirk of the system - we actually read the entries in ANY file named tnsnames* - so if you have older versions of the file saved as something like tnsnames_old.ora or tnsnames.bak - we'll see and use those too, something SQL*Plus used to do and we were asked to follow as well.