Oracle – cannot connect using sql developer

SQL Developer will look in the following location in this order for a tnsnames.ora file

1. $HOME/.tnsnames.ora
2. $TNS_ADMIN/tnsnames.ora
3. TNS_ADMIN lookup key in the registry
4. /etc/tnsnames.ora ( non-windows )
5. $ORACLE_HOME/network/admin/tnsnames.ora
6. LocalMachine\SOFTWARE\ORACLE\ORACLE_HOME_KEY
7. LocalMachine\SOFTWARE\ORACLE\ORACLE_HOME

To see which one SQL Developer is using, issue the command show tns in the worksheet

If your tnsnames.ora file is not getting recognized, use the following procedure:

1. Define an environmental variable called TNS_ADMIN to point to the folder that contains your tnsnames.ora file.

   In Windows, this is done by navigating to Control Panel > System > Advanced system settings > Environment Variables...

   In Linux, define the TNS_ADMIN variable in the .profile file in your home directory.

2. Confirm the os is recognizing this environmental variable

   From the Windows command line: echo %TNS_ADMIN%

   From linux: echo $TNS_ADMIN

3. Restart SQL Developer

4. Now in SQL Developer right click on Connections and select New Connection.... Select TNS as connection type in the drop down box. Your entries from tnsnames.ora should now display here.

I had this issue and the fix was to make sure in tnsnames.ora the SERVICE_NAME is a valid service name in your database. To find out valid service names, you can use the following query in oracle:

select value from v$parameter where name='service_names'

Once I updated tnsnames.ora to:

TEST =
   (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = *<validhost>*)(PORT = *<validport>*))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = *<servicenamefromDB>*)
    )
)

then I ran:

sqlplus user@TEST

Success! The listener is basically telling you that whatever service_name you are using isn't a valid service according to the DB.

(*I was running sqlplus from Win7 client workstation to remote DB and blame the DBAs ;) *)