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 Devleoper setup such that it can use TNSNAMES to connect:

  1. 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:

On the Database - Advanced page
On the Database – Advanced page

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.

There we go!
There we go!

Defining the Connection

Just set the connection type to TNS.

This is a lot easier to do than manually defining the connections - esp as they're likely to frequently change in 'the real world.'
This is a lot easier to do than manually defining the connections – esp as they’re likely to frequently change in ‘the real world.’

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…

SQL Dev doesn't know which client to use and won't use it even if it DID know...
SQL Dev doesn’t know which client to use and won’t use it even if it DID know…

I’m able to define a new connection AND connect with these preferences ON|OFF.

thatjeffsmith
Author

I'm a Master Product Manager at Oracle for Oracle SQL Developer. My mission is to help you and your company be more efficient with our database tools.

55 Comments

  1. Carrie DeArmond Reply

    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

    • thatjeffsmith

      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.

    • thatjeffsmith

      Since you’re an Exadata customer, I’d open an SR with MOS, not sure what would cause that error messages.

    • Carrie DeArmond

      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

  2. hi Jeff,
    Any idea why SQL Developer doesn’t respect IFILE in my tnsnames.ora?

    • 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.

  3. 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.

    • thatjeffsmith

      ???

      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.

  4. Evelyn Noelia Reply

    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).

    • thatjeffsmith

      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.

  5. Hi Jeff,
    Thanks for the detailed post. I am having issue with LDAP recognization. Could you please help me on this?

  6. Hello,
    Thank you for this article it was for a good help

    Regards,
    sam

  7. 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)
    )
    )

  8. 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.

    • thatjeffsmith

      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.

    • Vitor Jr.

      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.

  9. Ghayas Ahmad Reply

    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.

  10. 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.

    • 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

    • Yes the database is up and running, I just have a connection problem with one or two databases which doesnt work via TNS file.

  11. 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

    • thatjeffsmith

      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.

  12. 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

    • thatjeffsmith

      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.

  13. 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?

    • thatjeffsmith

      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.

  14. 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.

    • 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.

    • thatjeffsmith

      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.

  15. What if our installation of SQL Developer doesn’t come with a tsnnames.ora file or any Network folder?
    Thanks!

    • thatjeffsmith

      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.

  16. Mark Casazza Reply

    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

    • thatjeffsmith

      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.

  17. 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

  18. 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.

    • thatjeffsmith

      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 ๐Ÿ™‚

Write A Comment