Environment Variables, SQL*Net and Windows

From time to time I stumble upon questions regarding environment settings for Oracle installations on Windows, either Server or Client. The question are always similar. What variables are set? Where are they set? What about registry entries? How do I get the values in use? This post will hopefully clarify some things about that.

Where do I set environment variables?

There are several places to define environment variables. This is the list of possibilities in order of precedence.

  1. set directly in the current shell (CMD, set command)
  2. user variable set in System properties
  3. system variable set in System properties
  4. Registry value in HKLM\software\oracle\<Home name>

Which SQL*Net configuration files are used?

The search order for SQL*Net files like sqlnet.ora and tnsnames.ora is like this:

  1. Current directory
  2. Path defined by TNS_ADMIN variable (see above for specifying variables)
  3. %ORACLE_HOME%\network\admin

Which values are actually in use?

Since the “set” command displays only variables and values that are defined as environment variables and ignores any values defined inside the Registry, we need another way to get these values. We can use SQL*Plus for this:

C:\Users\marco.mischke>set NLS_LANG=american_america.we8pc850

C:\Users\marco.mischke>sqlplus /nolog

SQL*Plus: Release Production on Tue Dec 1 10:40:56 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

SP2-0310: unable to open file "[from registry].sql"
SP2-0310: unable to open file "[american_america.we8pc850]"
SP2-0310: unable to open file "[%NLS_DATE_FORMAT%]"

You see, SQL*Plus expands the variables to the final value, even if the value comes from the Registry. Wben there is no value anywhere defined, it simply displays the variable name.