I'm trying to connect to an oracle database with SQL Developer.
I've installed the .Net oracle drivers and placed the tnsnames.ora file at
C:\Oracle\product\11.1.0\client_1\Network\Admin
I'm using the following format in tnsnames.ora:
dev =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.XXX.XXX)(PORT = XXXX))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = idpdev2)
)
)
In SQL Developer, when I try to create a new connection, no TNS-names show up as options.
Is there something I'm missing?
This question is related to
database
oracle
oracle-sqldeveloper
tnsnames
tns
The answer is
Open SQL Developer. Go to Tools -> Preferences -> Databases -> Advanced Then explicitly set the Tnsnames Directory
My TNSNAMES was set up correctly and I could connect to Toad, SQL*Plus etc. but I needed to do this to get SQL Developer to work. Perhaps it was a Win 7 issue as it was a pain to install too.
In SQLDeveloper browse Tools --> Preferences, as shown in below image.
In the Preferences options expand Database --> select Advanced --> under "Tnsnames Directory" --> Browse the directory where tnsnames.ora present.
Then click on Ok.
as shown in below diagram.
You have Done!
Now you can connect via the TNSnames options.
The steps mentioned by Jason are very good and should work. There is a little twist with SQL Developer, though. It caches the connection specifications (host, service name, port) the first time it reads the tnsnames.ora file. Then, it does not invalidate the specs when the original entry is removed from the tnsname.ora file. The cache persists even after SQL Developer has been terminated and restarted. This is not such an illogical way of handling the situation. Even if a tnsnames.ora file is temporarily unavailable, SQL Developer can still make the connection as long as the original specifications are still true. The problem comes with their next little twist. SQL Developer treats service names in the tnsnames.ora file as case-sensitive values when resolving the connection. So if you used to have an entry name ABCD.world in the file and you replaced it with an new entry named abcd.world, SQL Developer would NOT update its connection specs for ABCD.world - it will treat abcd.world as a different connection altogether. Why am I not surprised that an Oracle product would treat as case-sensitive the contents of an oracle-developed file format that is expressly case-insensitive?
The steps mentioned by Jason are very good and should work. There is a little twist with SQL Developer, though. It caches the connection specifications (host, service name, port) the first time it reads the tnsnames.ora file. Then, it does not invalidate the specs when the original entry is removed from the tnsname.ora file. The cache persists even after SQL Developer has been terminated and restarted. This is not such an illogical way of handling the situation. Even if a tnsnames.ora file is temporarily unavailable, SQL Developer can still make the connection as long as the original specifications are still true. The problem comes with their next little twist. SQL Developer treats service names in the tnsnames.ora file as case-sensitive values when resolving the connection. So if you used to have an entry name ABCD.world in the file and you replaced it with an new entry named abcd.world, SQL Developer would NOT update its connection specs for ABCD.world - it will treat abcd.world as a different connection altogether. Why am I not surprised that an Oracle product would treat as case-sensitive the contents of an oracle-developed file format that is expressly case-insensitive?
In Sql Developer, navidate to Tools->preferences->Datababae->advanced->Set Tnsname directory to the directory containing tnsnames.ora
You can always find out the location of the tnsnames.ora file being used by running TNSPING to check connectivity (9i or later):
C:\>tnsping dev
TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 08-JAN-2009 12:48:38
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = XXX)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = DEV)))
OK (30 msec)
C:\>
Sometimes, the problem is with the entry you made in tnsnames.ora, not that the system can't find it. That said, I agree that having a tns_admin environment variable set is a Good Thing, since it avoids the inevitable issues that arise with determining exactly which tnsnames file is being used in systems with multiple oracle homes.
None of the above changes made any difference in my case. I could run TNS_PING in the command window but SQL Developer couldn't figure out where tnsnames.ora was.
The issue in my case (Windows 7 - 64 bit - Enterprise ) was that the Oracle installer pointed the Start menu shortcut to the wrong version of SQL Developer. There appear to be three SQL Developer instances that accompany the installer. One is in %ORACLE_HOME%\client_1\sqldeveloper\ and two are in %ORACLE_HOME%\client_1\sqldeveloper\bin\ .
The installer installed a start menu shortcut that pointed at a version in the bin directory that simply did not function. It would ask for a password every time I started SQL Developer, not remember choices I had made and displayed a blank list when I chose TNS as the connection mechanism. It also does not have the TNS Directory field in the Database advanced settings referenced in other posts.
I tossed the old Start shortcut and installed a shortcut to %ORACLE_HOME%\client_1\sqldeveloper\sqldeveloper.exe . That change fixed the problem in my case.
You can always find out the location of the tnsnames.ora file being used by running TNSPING to check connectivity (9i or later):
C:\>tnsping dev
TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 08-JAN-2009 12:48:38
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = XXX)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = DEV)))
OK (30 msec)
C:\>
Sometimes, the problem is with the entry you made in tnsnames.ora, not that the system can't find it. That said, I agree that having a tns_admin environment variable set is a Good Thing, since it avoids the inevitable issues that arise with determining exactly which tnsnames file is being used in systems with multiple oracle homes.
You can always find out the location of the tnsnames.ora file being used by running TNSPING to check connectivity (9i or later):
C:\>tnsping dev
TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 08-JAN-2009 12:48:38
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = XXX)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = DEV)))
OK (30 msec)
C:\>
Sometimes, the problem is with the entry you made in tnsnames.ora, not that the system can't find it. That said, I agree that having a tns_admin environment variable set is a Good Thing, since it avoids the inevitable issues that arise with determining exactly which tnsnames file is being used in systems with multiple oracle homes.
You can always find out the location of the tnsnames.ora file being used by running TNSPING to check connectivity (9i or later):
C:\>tnsping dev
TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 08-JAN-2009 12:48:38
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = XXX)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = DEV)))
OK (30 msec)
C:\>
Sometimes, the problem is with the entry you made in tnsnames.ora, not that the system can't find it. That said, I agree that having a tns_admin environment variable set is a Good Thing, since it avoids the inevitable issues that arise with determining exactly which tnsnames file is being used in systems with multiple oracle homes.
Open SQL Developer. Go to Tools -> Preferences -> Databases -> Advanced Then explicitly set the Tnsnames Directory
My TNSNAMES was set up correctly and I could connect to Toad, SQL*Plus etc. but I needed to do this to get SQL Developer to work. Perhaps it was a Win 7 issue as it was a pain to install too.
In Sql Developer, navidate to Tools->preferences->Datababae->advanced->Set Tnsname directory to the directory containing tnsnames.ora
None of the above changes made any difference in my case. I could run TNS_PING in the command window but SQL Developer couldn't figure out where tnsnames.ora was.
The issue in my case (Windows 7 - 64 bit - Enterprise ) was that the Oracle installer pointed the Start menu shortcut to the wrong version of SQL Developer. There appear to be three SQL Developer instances that accompany the installer. One is in %ORACLE_HOME%\client_1\sqldeveloper\ and two are in %ORACLE_HOME%\client_1\sqldeveloper\bin\ .
The installer installed a start menu shortcut that pointed at a version in the bin directory that simply did not function. It would ask for a password every time I started SQL Developer, not remember choices I had made and displayed a blank list when I chose TNS as the connection mechanism. It also does not have the TNS Directory field in the Database advanced settings referenced in other posts.
I tossed the old Start shortcut and installed a shortcut to %ORACLE_HOME%\client_1\sqldeveloper\sqldeveloper.exe . That change fixed the problem in my case.
Source: Stackoverflow.com