On SQL Developer and TNSNAMES.ORA

thatjeffsmith SQL Developer 40 Comments

Tell Others About This Story:

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.

Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Comments 40

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

    1. thatjeffsmith Post
      Author

      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.

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

        1. thatjeffsmith Post
          Author
  2. 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.

    1. thatjeffsmith Post
      Author
      1. 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.

        1. thatjeffsmith Post
          Author
          1. thatjeffsmith Post
            Author
          2. thatjeffsmith Post
            Author
  3. 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.

    1. thatjeffsmith Post
      Author
      1. 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

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

          2. thatjeffsmith Post
            Author
  4. 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

    1. thatjeffsmith Post
      Author

      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.

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

    1. thatjeffsmith Post
      Author
  6. 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?

    1. thatjeffsmith Post
      Author
  7. 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.

    1. thatjeffsmith Post
      Author
      1. 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.

        1. thatjeffsmith Post
          Author

          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.

    1. thatjeffsmith Post
      Author
    2. thatjeffsmith Post
      Author

      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.

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

    1. thatjeffsmith Post
      Author
  9. 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

    1. thatjeffsmith Post
      Author
  10. 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.

    1. thatjeffsmith Post
      Author

      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 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *