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.
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.
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.
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 :-)
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
Available TNS Entries
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.
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.
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:
So each project sees the databases that they need to access, and all members of the project use the same names, etc.
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.