9 Replies Latest reply on Dec 20, 2017 7:55 PM by rp0428

    Connection strings in SQL developer

    Gauthemen

      Hello All,

      Quick check... which is better for connection usage and to maintain "tnsnames.ora" or "connections.xml" ? Been puzzled as several people prefer editing connections manually on tnsnames.ora file and some prefer having connections.xml add and create the connection strings. Any standard practice or advises or sort..

        • 1. Re: Connection strings in SQL developer
          2683628

          these ere the comments from the XE 11g tnsnames.ora file:

          not sure if you can add passwords in this file - in the connection.xml file you probably have. I think the tNS file is useful for ensuring a number of users have the same connectiovity settings

           

          This file contains the syntax information for

          # the entries to be put in any tnsnames.ora file

          # The entries in this file are need based.

          # There are no defaults for entries in this file

          # that Sqlnet/Net3 use that need to be overridden

          #

          # Typically you could have two tnsnames.ora files

          # in the system, one that is set for the entire system

          # and is called the system tnsnames.ora file, and a

          # second file that is used by each user locally so that

          # he can override the definitions dictated by the system

          # tnsnames.ora file.

           

          # The entries in tnsnames.ora are an alternative to using

          # the names server with the onames adapter.

          # They are a collection of aliases for the addresses that

          # the listener(s) is(are) listening for a database or

          # several databases.

           

          # The following is the general syntax for any entry in

          # a tnsnames.ora file. There could be several such entries

          # tailored to the user's needs.

          • 2. Re: Connection strings in SQL developer
            thatJeffSmith-Oracle

            tnsnames.ora is optional

             

            connections.xml is not

             

            You don't need to maintain your connections.xml file - that's done for you by the application. Although I do recommend you back it up, esp if you have LOTS of connections.

             

            A connection in SQLDev has the db details, your username and optionally your password.

             

            The tnsnames.ora file ONLY has the db details.

            • 3. Re: Connection strings in SQL developer

              Quick check... which is better for connection usage and to maintain "tnsnames.ora" or "connections.xml" ?

              1. TNSNAMES.ORA belongs to an Oracle client and does NOT belong to and is NOT maintained by Sql developer

              2. For the majority of 'basic' connections you don't even need to use the ORA file - you can use the 'thin' driver.

              3. That ORA file can have settings and info that are needed by Oracle client but are NOT used by Sql Developer

              4. Medium and Large orgs generally maintain their 'master' copy of the ORA file centrally and then distribute that to the users that need it.

              So most users NEVER modify it.

              • 4. Re: Connection strings in SQL developer
                Jim 13131

                I avoid TNSnames like the plague. There can be multiple TNSNames files on a PC - one for each client home or more and I usually have at least two clients (32 and 64 bit)

                 

                I find users are always confused about which TNSnames file they are using and a large percentage of connection problems come from the user thinking they are using one file but actually using the other. I use Basic connections in SQL Devloper and EZConnect strings everywhere else.

                 

                SQL Developer does have a preference to set the TNSnames file you are using (Preferences > Database > Advanced) but it is not connection specific, so if you change the TNSnames files you'll need to ensure all your existing TNSNames connections are in the new one.

                 

                Just say NO to TNSNames - your life will be much easier :-)

                • 5. Re: Connection strings in SQL developer
                  thatJeffSmith-Oracle

                  I find users are always confused about which TNSnames file they are using and a large percentage of connection problems come from the user thinking they are using one file but actually using the other.

                   

                  Very much so - we built a new command, in a worksheet, run ' show tns' - you'll see which files we found to resolve connection requests

                   

                  SQL> show tns

                   

                  TNS Lookup locations

                  --------------------

                  1. USER Home dir

                    C:\Users\jdsmith

                  2. TNS_ADMIN

                    D:\

                   

                  Location used:

                  -------------

                    C:\Users\jdsmith

                   

                  Available TNS Entries

                  ---------------------

                  DevDay11

                  DevDay12CDB

                  DevDay12PDB

                  SQL>

                  • 6. Re: Connection strings in SQL developer
                    Jim 13131

                    I like that command - unfortunately most of my users are connect with things other than SQL Developer - IE ThirdParty programs that use the Oracle DB to run business - and it is their OT department configuring the program that are confused.

                     

                    Jim

                    • 7. Re: Connection strings in SQL developer
                      jflack

                      One of the first things I do when I start using a machine with one or more Oracle homes is search for all of the TNSNAMES.ORA files on the machine.  I combine them into one, and move that one to a central location, usually c:\Oracle\admin\network.  I delete ALL other copies.  Then I set up the environment variable, TNS_ADMIN to point to that folder.  The two editors that Oracle provides that can change the connections use TNS_ADMIN to find the file, so does TOAD's. Oracle clients, including SQL Developer use it too.

                      • 8. Re: Connection strings in SQL developer
                        L. Fernigrini

                        Exactly what we do, keep a single TNSNAMES.ORA file with valid connections for each project, all of them on a particular folder of a shared location. We use TNS_ADMIN to point to the right folder. The structure would be something like:

                         

                        /SharedTNS

                            /Project1

                               tnsnames.ora

                            /Project2

                               tnsnames.ora

                            /Project3

                               tnsnames.ora

                         

                        So each project sees the databases that they need to access, and all members of the project use the same names, etc.

                        • 9. Re: Connection strings in SQL developer

                          Or you could do what many others have been doing for years.

                           

                          Use batch files to set the environment properly for each app that needs it.

                           

                          Then you can custom set any environment variables you want without having to change them at the os level.

                           

                          It is generally NOT a good idea to alter the main environment variables just because you install an new application. That destroys modularity and pretty much makes it impossible to satisfy conflicting requirements apps might have for PATH or other things.

                           

                          1. create a new batch file for the new app

                          2. create a custom 'set_environment.bat' file for the new app

                          3. call/execute the custom 'set' script from the new batch file

                          4. call/execute the app form the new batch file

                           

                          Makes it REALLY EASY to run various versions of the app since you can pass parameters to the batch file (when you need to) to launch in 'debug' mode or other things.