oracle_home path and tns_admin

Do you know if its possible on windows 7 to check what the following system variables are set to via the command prompt. We are having many issues getting a query tool working and communicating with an oracle DB server.

From some resources it looks like you can set them via the command prompt - would htat be a permanent change to the system, or just a temporary one? If only temporary, how do you permanently change these variables?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
To see CMD environment variables, from the CMD prompt:  
set <enter>

>>If only temporary, how do you permanently change these variables?
for CMD prompt variables,  SYSTEM Environment variables:
(For XP but it is similar in Win7.  Just couldn't find the Win7 Microsoft link):

For regular Oracle Windows variables it is in the Registry.

From your previous question this involves the Instant Client.  In that question I mentioned manually creating the network/admin folder under the instant client folder and copying the tnsnames.ora file in there.

then you don't need to worry about TNS_ADMIN.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark GeerlingsDatabase AdministratorCommented:
In both Linux and Windows, system (or environment) variables can be set via the command prompt.  But, in both cases this is not a permanent change.  The change lasts only for the direction of that login session in the O/S.

To make persistent changes to these values in Windows7, got to; Control Panel Home (or: My Computer), then: System, then Advanced System Settings, then the "Advanced" tab, then the "Environment Variables" button, then add (or change) a value in the "System variables" part of the screen.  That way, the value will persist and be used every time.
Steve WalesSenior Database AdministratorCommented:
To expand a little further on slightwv's answer - if you have a situation where you have to have multiple clients installed (32 bit and 64 bit) - this is a situation I have to deal with, some people at my company still have a couple of tools that are 32 bit only in a 64 bit world.

To make maintenance a little easier, so we're not installing / maintaining two tnsnames files, we've created a single directory where tnsnames is installed and we use TNS_ADMIN as a system environment variable in Windows and they just update it once as needed there and both clients know about it.

Disclaimer:  We're not using the instant client, so not sure if that changes any of the above but I'd doubt it.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

pma111Author Commented:
the network/admin folder thing hasnt worked in this instance, the software still doesnt pick the list of database servers from tnsnames.ora.
Steve WalesSenior Database AdministratorCommented:
Do you have TNS_ADMIN set ?

The FAQ for the Instant Client specifically says to set TNS_ADMIN:

How do I ensure that my "tnsnames.ora" file is being used in Instant Client?
Always set the TNS_ADMIN environment variable or registry to the location of the tnsnames.ora file (full directory path only, do not include the file name). This practice will ensure that you are using the appropriate tnsnames.ora for your application when running with Instant Client.

  Modified: 08-APR-04    Ref #: ID-5028

Date on the FAQ is old but take it for what it's worth.

If the Default of network\admin isn't working, have you tried setting TNS_ADMIN and seeing what happens ?
pma111Author Commented:
I have tried setting TNS_ADMIN via the command prompt but it still doesnt work. I am not sure if the command is changing the value, it doesnt return any sort of message it just moves onto a new line in command prompt expecting a new command.
pma111Author Commented:
Still a bit lost on what command from command prompt shows the variable settings for things like oracle home, tns_admin, as the first response is how to set them, not how to view what they are currently set to.
Steve WalesSenior Database AdministratorCommented:
Type "set" at the prompt to see the values.

However setting the values in a command prompt isn't going to persist the values for when you launch your query app.

You need to set it using the system environment level.

Search in control panel for environment variables and set a system level or user level environment variable called TNS_ADMIN pointing to the directory where you have tnsnames.ora.

It appears that the way you have been trying to do it won't work.

Try it the way I just described and get back to us please.
pma111Author Commented:
thats interesting, just entering SET doesn't even list TNS_ADMIN or ORACLE_HOME.
pma111Author Commented:
even when I run set ORACLE_HOME=C:\oracle\

And then type


Oracle_home is still not listed as an option - even a temporary setting, just doesn't appear.
pma111Author Commented:
I don't have the required level of access on my machine to do it the other way I will log a service request to have them look into it. Is it strange that viewing settings and trying to set values via command prompt seems to be ignored?
pma111Author Commented:
must be permissions, just tried on a test laptop and I can set variables via the command prompt..
slightwv (䄆 Netminder) Commented:
>>not how to view what they are currently set to.

To clarify:  I did post that.  Very first sentence of the very first post...

>>I have tried setting TNS_ADMIN via the command prompt but it still doesnt work

And it shouldn't.  The CMD prompt is 'local' so anything set in it, will be local to that window.  TNS_ADMIN needs to be a 'system' variable set through the Control Panel for anything outside that window to be able to use it.
pma111Author Commented:
It still isnt working. Now checking SET does show the variables, all set with correct paths, but the software still doesnt allow a connection or list the databases in the drop down from tnsnames.ora.

Jsut to clairfy, once I download the intant client zip file, I extract all contents to a folder called c::\users\me\oracle

This then creates a new sub folder called instantclient_12_1

so in control panel our windows admin has set system variables of:

PATH (added the same path as above to the list).

in this folder c:\users\me\oracle\instantclient_12_1 is a TNSNAMES.ORA file. Software is 32-bit, windows is 32-bit, and I 100% downloaded the 32-bit instant client.

Completely lost what to try next.
pma111Author Commented:
the error given (when trying an ezconnect string in the login field, which the applicaiton supports if you cant find tnsnames.ora - is ORA-12154 TNS could not resolve the connect identifier specified.. this error usually means the database alias wasnt found in your tnsnames.ora file.
slightwv (䄆 Netminder) Commented:
Download the Instant Client add on that has sqlplus.  Unzip that in the same folder.

From a CMD prompt run sqlplus and try to connect using an alias from the tnsnames.ora file.

cd \users\me\oracle\instantclient_12_1

If it connects, the Instant Client is working properly and the issue is with your software.

Any reason you aren't using SQL Developer?
Mark GeerlingsDatabase AdministratorCommented:
Even without SQL*Plus, you can test your tns configuration at the command prompt.  Just type:
tnsping [dbalias] [Enter]

Here is a (slightly-edited) screen shot of doing that in our system.  What you want to see is the "OK" on the last line:
C:\>tnsping dev
[a few lines with version and copyright information]

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL=tcp)( (CONNECT_DATA = (SERVICE_NAME=dev)))
OK (10 msec)

Open in new window

slightwv (䄆 Netminder) Commented:
>>Even without SQL*Plus, you can test your tns configuration at the command prompt.  Just

Maybe it has changed but I don't think the Instant Client doesn't come with tnsping.
pma111Author Commented:
results with sql plus

(it prompts for a username)

so entered the format


returns error ORA-12545 - connect failed because target host or object does not exist
slightwv (䄆 Netminder) Commented:
Looks like it found the tnsnames.ora file but the entry with 'alias' has issues.

Double check the ADDRESS entries.  Can you ping the ipaddress/server name specific by the HOST entry?
Mark GeerlingsDatabase AdministratorCommented:
I agree, that usually indicates an error in your entry for the "alias" you tried.  Either the host name, the port number, or the SID (or service name) are apparently not correct.  Or possibly, you have the host name correct, but DNS resolution isn't working for this host name.  If that is true, use the IP address instead of the host name in the tnsnames.ora file.

And I think slightwv is correct, the "tnsping" utility may not be included in the Instant Client.
pma111Author Commented:
well you all helped narrow it down, turns out the DBA sent me an old tnsnames.ora file refering the old server before it was migrated! sorry about that, but I wouldnt have been able to tie down the issue without your assistance.
slightwv (䄆 Netminder) Commented:
No problem.  Troubleshooting is a checklist.  However, I was running out of things to check...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.