If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > how to point oracle to correct tnsnames.ora file?

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Mar 2005
Posts: 25
how to point oracle to correct tnsnames.ora file?

What do I need to set so that sqlPlus, sqlNavigator etc knows where to find the tnsnames.ora file?

Do I need an environment variable?
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Feb 2006
Posts: 15
Yes, set the environment variable TNS_ADMIN to the full directory path where the tnsnames.ora file resides.


.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Mar 2005
Posts: 25
I tried it as a user variable and a system variable. Do I need a restart?

What is ORACLE_HOME used for?
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 7,591
what is the OS?
__________________
You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
Don't say, show. Don't promise, prove.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Mar 2005
Posts: 25
Windows XP
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Feb 2006
Posts: 15
No, you don't need to restart.

Set the environment variable in the Advanced tab of the System Properties control panel.
Bring up a new command prompt window.

Check it is set:

echo %TNS_ADMIN%


Then run tnsping to check if your connect string can be resolved:
tnsping <connect string>


If TNS_ADMIN is not set then sqlplus will look for tnsnames.ora in the default location:

%ORACLE_HOME%\newtork\admin


For details on ORACLE_HOME:
http://download-east.oracle.com/docs....htm#sthref133
.
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Mar 2005
Posts: 25
This doesn't work for my machine. I think I need to edit the registry.

My machine used to look locally for the tnsnames.ora file. Then I uninstalled oracle locally. Now I need to point sqlplus, sql navigator, toad, etc to go to another location on the c drive for the tnsnames.ora file.

Any idea what key that would be?
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Feb 2006
Posts: 15
Registry entry is TNS_ADMIN

Set the registry by all means but if the environment variable is set correctly then it may have nothing to do with locating the tnsnames.ora file

Toad uses the location you set in the connection screen.
Don't know what sql navigator uses.
sqlplus and tnsping use TNS_ADMIN

What error do you get from sqlplus?
What error do you get from tnsping?
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Sep 2003
Location: Virginia, USA
Posts: 246
For UNIX users, the order in which Oracle searches locations for Oracle Net files like sqlnet.ora and tnsnames.ora are in this order:
1. $HOME for hidden files only (i.e., .sqlnet.ora and .tnsnames.ora)
2. $TNS_ADMIN
3. $HOME
4. /etc or /var/opt/oracle (depends on platform)
5. $ORACLE_HOME/network/admin

For Windows users, the search order is a bit different. It goes like this,
1. current path (associated with the running client application)
2. Environment variable TNS_ADMIN defined for the session
3. Environment variable TNS_ADMIN defined for the system
4. Windows Registry Key TNS_ADMIN
5. %ORACLE_HOME%\network\admin

Now, from what I've read of the posts you are using Windows. If you do nothing (i.e., don't define TNS_ADMIN anywhere) then Oracle will look in the default location %ORACLE_HOME%\network\admin. On most windows computers this is something like c:\oracle\oraDb10_home1\network\admin. If you are unsure what your Oracle Home is, just look in the registry under HKLM/software/oracle for a hive named either HOME0 for Oracle 9i, or a hive named using the value you supplied during installlation of Oracle. In that hive will be a key named ORACLE_HOME.

I strongly recommend against setting TNS_ADMIN as a system variable. I believe Oracle does too. You really should stick to registry keys, since some Oracle apps won't pickup TNS_ADMIN as a system variable.
__________________
MarkRem
Author, Oracle Database 10g: From Nuts to Soup
http://www.remidata.com/book_nuts2soup.htm
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Feb 2006
Posts: 15
Permanent environment variables on Windows ie. those set using the System Properties Panel (user or system), are written to the registry.
They are the same thing.

If you have correctly set TNS_ADMIN and sqlplus still isn't working then post the error (or switch on tracing).
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On