Tnsnames.ora [DOCS] is a configuration file for SQL*Net that describes the network service names for the databases in your organization. Basically, it tells Oracle applications how to find your databases.
This post is just a quick overview on how to get SQL Developer to ‘see’ this file and define a connection.
There’s only a single prerequisite for having SQL Developer setup such that it can use TNSNAMES to connect:
- You have somewhere a tnsnames.ora file
You don’t need a client, instant or otherwise, on your machine. You just need the file.
Now, if you DO you have a client or HOME on your machine, SQL Developer will look for those and find the tnsnames file for you. IF we can’t find it at the usual places, you can simply tell us where it is via this preference:
Once you’ve done this, assuming you have a file (or 10) in that directory, we’ll read it, parse it, and list the entries in the connection dialog.
The File(s)
That’s right, files. Just like SQL*Plus, we’ll read any file that starts with ‘tnsnames’ – that includes files you’ve renamed to .bak or .old. Kris talks about that more here. I have just the one, which is all I need anyway.
Defining the Connection
Just set the connection type to TNS.
No Client or Home Required
That’s right. You don’t need an Oracle Client or $ORACLE_HOME to have SQL Developer see and read a TNS file.
Just so you know I’m not cheating…
I’m able to define a new connection AND connect with these preferences ON|OFF.
55 Comments
Hi Jeff,
I can logon thru SQL Plus to a DB that just moved to a new exadata box.
When I try to logon thru my SQL Developer Client, to this very same DB (using the exact same network alias), I get ORA-12547 TNS:lost contact. My client is version 17.3 and we are running on Oracle 12.2.
Thanks. in advance.
KR, Carrie
No idea, assuming sqlplus is on same machine as your sqldev? SQLDev uses jdbc and sqlplus doesn’t…either way, this is exadata, i would open an SR or search the OTN forums on that error message and jdbc/exadata.
Since you’re an Exadata customer, I’d open an SR with MOS, not sure what would cause that error messages.
Hi Jeff,
Thanks for your responses!!
I was finally able to resolve this TNS: Lost Contact error by making the following change in SqlDeveloper:
GOTO Preferences > Database > Advanced
UNCHECK Use OCI/Thick driver
We think that OCI was conflicting with our TCPS set up in our TNS, etc
KR, Carrie
hi Jeff,
Any idea why SQL Developer doesn’t respect IFILE in my tnsnames.ora?
We support that, it should work
Hi Jeff,
Thanks for the reply.
Ah, I see you can type the “connect identifier” and it will work – what I’m missing is that those connections in the ifile don’t get populated in the “network alias” lov. This would be a nice-to-have.
Just wondering if it is possible to open the tnsnames file from within SQL Developer – as opposed to first finding its location that had been set in preferences, then going to a text editor to open it.
???
running ‘show tns’ in the sql worksheet will output back the file we have found and used and a list of services…then you could do File – Open on that I suppose.
Hi Jeff, thanks for the post. Do you know if it works also with other applications as alteryx or tableau? Because I need a tnsnames file and I cannot find it (even though I have sqldeveloper with the connection working).
We don’t build a TNSNames.ora file for you. The oracle client network assistant or your DBA can help you with that.
Tableau supports JDBC drivers so you can just use a JDBC URL. Alteryx supports ODBC vs JDBC, so you would need to setup an ODBC source…which you can do with the information stored in your sqldev connection properties.
Best to go to tableau and alteryx for help with those plastforms.
Hi Jeff,
Thanks for the detailed post. I am having issue with LDAP recognization. Could you please help me on this?
Hello,
Thank you for this article it was for a good help
Regards,
sam
Hi Jeff,
I have installed Oracle SQL Developer Version 4. When I faced issue in connecting to database, I came through your article on downloading XE for database connection. I installed XE in my machine. After installing, placed the tnsnames.ora file in the Tools -> Preferences –> Database -> Advanced->TNSNamesDirectory.
When I try to connect via Connection Type as TNS with the available services, I am getting error as
username/password required for XE and I/O Network Adapter error for ORACLR_CONNECTION_DATA & EXTPROC_CONNECTION_DATA .
Kindly assist me.
I am using the below tnsnames, downloaded from Oracle.
XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = HP-HP)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)
Your Sid/service is just xe
FWIW – As of v4.1.3.20 on OS X I need to have the instant client path defined in order for the tnsnames.ora file to be read. If I just have the path to the tnsnames.ora file, the TNS list is not populated.
Nevermind Jeff. I found it.
Regards
Vitor Jr
Hi Jeff. Thanks for the post.
When I try do select an entry from tnsnames.ora I get this:
https://s6.postimg.org/762n1ziip/tns1.png
Here is an example of a entry on my tnsnames.ora:
https://s6.postimg.org/mstwfcwap/tns2.png
And here is my configuration:
https://s6.postimg.org/po6zm80ap/tns3.png
Best Regards!
Vitor Jr.
Sorry, your post went into my SPAM folder, just now seeing this.
Try to create a very small TNS file with only one entry in it. Make sure that’s working. I’m guessing one of entries in your file is throwing off the parser we have.
No problem. Issue was with the #’s simbols, cause used to be on a linux system, for comments.
So in the windows system, sql developer got problems with that.
Regards! Huge fan!
Vitor Jr.
mmmm, weird, should work cross-plat. thanks for the detective work!
Hi Jeff,
i got a error – test failure:io error: the network adapter could not estailish the connection while i am creating database in oracle sql developer.
Please tell me any solution to fix it.
Read this.
I have read it. I have installed sql developer but there is information on control panel regarding installation and couldn’t find ORA file in directory.
you don’t get a tnsnames files after installing sqldev. you have to create one.
How to create it???
with a text editor
or if you have an oracle client, use the network assistant program
or, don’t bother and just use the Direct connect option in SQLDev
I have notepad++
Cool. So get your db particulars, and follow the syntax here.
Hi
I have a error when trying to connect to one of my Databases through TNS from another machine though we using the same TNS file.
Do I get to guess what the error is?
Hi
The error read: An error was encountered performing the requested operartion:
IO Error: the network adapter could not establish the connection
Vendo code 17002.
Note: the user does not have the oracle client installed on their machine, I followed the steps given as per above.
Regards
Ngako
can you ping the server? is the database up? are the connection details correct?
Yes the database is up and running, I just have a connection problem with one or two databases which doesnt work via TNS file.
so either you can’t get to that server or there’s something wrong with your tns file
Hi Jeff,
I set the directory to where the tnsnames.ora file is, but it doesnt update
does this setting override any existing tnsnames.ora files in other locations that sql developer might look for first?
It shows me a list of connections in the tns dialog, but not all the connections that exist in the tnsnames file
you have more than one tnsnames* files in that directory – one of those files has a bad entry, and we’re probably stopping after that. rename all of the files you don’t want used to old_tnsnames… or similar.
hi Jeff,
I was wondering that if i was given a tnsnames.ora file and told to connect through developer, i could do it without a username/password to secure a connection with.
thanks
Imagine I gave you a map of your neighborhood to a ‘friend’ – then told someone to get into your house without a key.
You kinda need a username/password to login to a database.
I just installed Oracle Client and the version of my Oracle SQL Developer is 1.5.5 (Build MAIN-5969). Under Tool-> Preferences -> Database -> Advance Parameters, there is no “Tnsnames Directory” input box. What else can I do to get SQL Developer to recognize the ora file?
Your version of SQL Developer is about 8 years old. Upgrade, then you can follow the instructions on this page to get your TNS file working.
Version 1.5.5 does not have that parameter under Database/Advanced. And the Tools/Preferences/Database node actually has Advanced Parameters, not just “Advanced”. Doing a search for tnsnames in preferences turns up nothing.
You are living in the year 2008? If not, why are you using version 1.5.5?
Really! I wondered when a colleague said he had v4.0.6. But I found that there is a Connection Type = TNS that looks up tnsnames.ora and lets me create a connection. I was under the impression that *everything* in tnsnames would appear under the Connections node. As it is, it works like PL/SqlDeveloper which I’ve used for years.
As to v1.5.5, I just started running what came with the 11g client that was included in the image on a new laptop. I never actually ‘installed’ sqlDeveloper. And I only looked at it when I found that sqlplusW was not included in 11g and somebody mentioned sqlDeveloper as the new way to go.
We need to talk to the person building your laptop images with software from the past decade 😉
We will populate the TNS dropdown if we can find the tnsnames.ora file – if we can’t find it, you can point to it directly using that preference.
What if our installation of SQL Developer doesn’t come with a tsnnames.ora file or any Network folder?
Thanks!
Then you get to build your own, or just skip it and use the direct connect properties.
BTW, no ‘installation’ of SQL Developer will come with a tnsnames.ora file – that’s something that a DBA will put together for an organization generally. So talk to your DBA.
Hi Jeff,
I put SQL Developer on a colleague’s machine last week and it didn’t pick up the Oracle HOME he already had. I didn’t know I could point directly to the tnsnames.ora file so I didn’t try that, but when I set TNS_ADMIN it did pick that up. I’m not going to go back and see just how the old HOME was configured, but are there any pros/cons about setting the location with the app vs. setting TNS_ADMIN and defaulting everything to one tnsnames.ora file?
Thanks,
Mark
Not any cons that I can think of, you just don’t want multiple TNSs floating around to maintain, so just point it to the ‘right’ one.
Hi Jeff,
Just noticed that though we can group saved connections under folders (in the Connections window), we cannot have nested folders.
Which means that I cannot have a folder ACME for the ACME project and multiple folders inside ACME called ACME-DEV, ACME-UAT, ACME-PROD for the various environments for the ACME project.
Can this be marked as an enhancement request for the next release 🙂
Thanks,
Sujoy
You can go vote on that feature here
Thanks for the link, Jeff! Added a 10 star vote 🙂
Hi Jeff,
In the window Manage Columns can you add fields when you write some part of name of columns and this column will be find and then you can choose that this column will be hidden or shown ? Please think about this.
Regards,
Jad.
Can you add fields? No, you can only control the columns displayed that are available from the SELECT clause. Not sure I understand your question though…or how this applies to Tnsnames.ora 🙂