SQLPlus cannot find TNSNAMES.ORA

Question

I just uninstalled an older Oracle installation on my computer (which was working fine) and installed a new one, and the new installation can't seem to find the TNSNAMES.ORA file even though it is in the \network\admin folder of the same installation directory as \bin\sqlplus.exe - and SQLPlus works if I connect by entering the IP and SID directly.

Is there some setting that I am missing that points to TNSNAMES.ORA? I always thought it looked in \network\admin by default.

Answer 1

If you uninstall previous Oracle SQL client and reinstall it, there should be multiple folders as below. In Windows C:\app\USER\product\ORACLE_CLENT_VERSION\client_INSTANCE-NO\network\admin Replace the upper case string with your computer path.

In Linux, it should be in the ./oracle/network/admin directory.

And connection string should be like

net_service_name= 
 (DESCRIPTION= 
   (ADDRESS=(protocol_address_information))
   (CONNECT_DATA= 
     (SERVICE_NAME=service_name))) 

I guess your issue is you are still using previous version client's TNSNAMES.ORA. In the C:\app\USER\product\ORACLE_CLENT_VERSION\client_INSTANCE-NO\network\admin, first instance installed created a client_1 (client_INSTANCE-NO) folder and second instance created a client_2 folder and so on. You need to ensure you editing latest instance TNSNAMES.ORA.

Answer 2

Found the problem - the person who installed it also installed Oracle Data Tools, and put the ODT folder first in the PATH environment variable, so, for some reason, Oracle looked there for TNSNAMES (and found the empty one in \network\admin\sample). Moving the main Oracle client directory to the front of PATH fixed it.