Results 1 to 10 of 10
  1. #1
    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?

  2. #2
    Join Date
    Feb 2006
    Posts
    15
    Yes, set the environment variable TNS_ADMIN to the full directory path where the tnsnames.ora file resides.


    .

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

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,616
    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.

  5. #5
    Join Date
    Mar 2005
    Posts
    25
    Windows XP

  6. #6
    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
    .

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

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

  9. #9
    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

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •