[windows] What's the best way to determine which version of Oracle client I'm running?

The subject says it all: What is the best way to determine the exact version of the oracle client I'm running? Our clients are all running Windows.

I found one suggestion to run the tnsping utility, without parameters, which does display the version information. Is there a better way?

Does the client install put this information in any sort of text file?

In Windows -> use Command Promt:

tnsping localhost

It show the version and if is installed 32 o 64 bit client, for example:

TNS Ping Utility for 64-bit Windows: Version 10.2.0.4.0 - Production on 03-MAR-2015 16:47:26

Source: https://decipherinfosys.wordpress.com/2007/02/10/checking-for-oracle-client-version-on-windows/

Just run this: select * from v$version

You should put a semicolon at the end of select * from v$version;.

Like this you will get all info you need...

If you are looking just for Oracle for example you can do as:

SQL> select * from v$version where banner like 'Oracle%';

Issue #1: Multiple Oracle clients are installed.

A very common issue I see in my environment is that I see both workstations and (app) servers with multiple Oracle clients, sometimes as many as four, and possibly with different versions and architectures. If you are relying on the PATH and running a utility like SQLPLUS or TNSPING you'll have one of two unacceptable results:

• either your PATH successfully resolves the executable and you get ONE version result
• or, the PATH didn't resolve the executable, and you get no results.

Either way, you are blind to possibly multiple client installations.

Issue #2: Instant Client doesn't have TNSPING, and sometimes doesn't include SQL*Plus.

If a computer has the Oracle Instant Client (not the full client), then TNSPING is not included, and SQLPLUS is an optional-addon. So can't rely on those tools being there. Furthermore, the Instant Client is sometimes installed as an unzip-and-go solution, so there's no Oracle Inventory and nothing in HKLM.

Issue #3: Client was installed using "Custom", and ODBC, OLEDB, ODP.Net, and JDBC were not installed.

Obvious case, there will be no ODBC or JDBC readme's to scrape version info from.

Solution:

One thing that the Instant client and the full client have in common is a DLL file called oraclient10.dll, oraclient11.dll, generally: oraclient*.dll. So let's traverse the hard disk to find them and extract their version info. PowerShell is amazing at this and can do it in one line, reminds me of home sweet Unix. So you could do this programatically or even remotely.

Here's the one-liner (sorry about the right scroll, but that's the nature of one-liners, eh?). Supposing you're already in a PowerShell:

gci C:\,D:\ -recurse -filter 'oraclient*.dll' -ErrorAction SilentlyContinue | %{ $_.VersionInfo } | ft -Property FileVersion, FileName -AutoSize

And if you're not in PowerShell, i.e. you're simply in a CMD shell, then no problem, just call powershell " ... ", as follows:

powershell "gci C:\,D:\ -recurse -filter 'oraclient*.dll' -ErrorAction SilentlyContinue | %{ $_.VersionInfo } | ft -Property FileVersion, FileName -AutoSize"

Example Outputs

Here's some outputs from some of my systems. This bad citizen has 3 Oracle 11.2.0.3 clients. You can see that some of them are 32-bit and others are 64-bit:

FileVersion            FileName
-----------            --------
11.2.0.3.0 Production  C:\NoSync\app\oracle\product\11.2\client_1\bin\oraclient...
11.2.0.3.0 Production  C:\oracle\product\11.2.0\client_1\bin\oraclient11.dll
11.2.0.3.0 Production  C:\oracle64\product\11.2.0\client_1\bin\oraclient11.dll

Another system, this one has 10g client on the D:\

FileVersion           FileName
-----------           --------
10.2.0.4.0 Production D:\oracle\product\10.2\BIN\oraclient10.dll

Caveats/Issues

• This obviously requires PowerShell, which is standard in Windows 7+ and Server 2008 R2+. If you have XP (which you shouldn't any more) you can easily install PowerShell.

• I haven't tried this on 8i/9i or 12c. If you are running 8i/9i, then there's a good chance you are on an old OS as well and don't have PowerShell and Heaven help you. It should work with 12c, since I see there is such a file oraclient12.dll that gets installed. I just don't have a Windows 12c client to play with yet.

Go to "Control Panel" -> "Administrative Tools" and open "Datasources (ODBC)". By default, the tab "User-DSN" will be opened, click "Add" and a dialogue will pop up:

you can use the following command in SQL Developer or SQLPLUS in command prompt to find out the Oracle server version number.

select * from v$version;

in my case it gave me the below mentioned info.

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
"CORE   11.2.0.1.0  Production"
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

TNSPing command line will show the version. similarly, sqlPlus.exe will print its version. You can also go to the readme files in the 'relnotes' directory of your client install. Version 10.2 has a file named README_jdbc.txt, for example, which will tell you which version has been installed.

I am assuming you want to do something programatically.

You might consider, using getenv to pull the value out of the ORACLE_HOME environmental variable. Assuming you are talking C or C++ or Pro*C.

Run the installer, click "Installed Products...". This will give you a more detailed list of all installed components of the client install, e.g., drivers, SQL*Plus, etc.

Typical Oracle installations will store inventory information in C:\Program Files\Oracle\Inventory, but figuring out the installed versions isn't simply a matter of opening a text file.

This is AFAIK authoritative, and shows any patches that might have been applied as well (which running the utilities does not do).

EDIT: A CLI option would be to use the OPatch utility:

c:\> path=%path%;<path to OPatch directory in client home, e.g., C:\oracle\product\10.2.0\client_1\OPatch>
c:\>set ORACLE_HOME=<oracle home directory of client, e.g., C:\Oracle\product\10.2.0\client_1>
c:\>opatch lsinventory

This gives you the overall version of the client installed.

In Unix

If you don’t know the location or version of installed Oracle product, you can find it from the inventory which is usually recorded in /etc/oraInst.loc

> cat /etc/oraInst.loc

inventory_loc=/export/oracle/oraInventory       **--> Inventory location**
inst_group=dba


> cd /export/oracle/oraInventory
> cd ContentsXML

Here look for a file inventory.xml

> cat inventory.xml
<?xml version="1.0" standalone="yes" ?>
<!-- Copyright (c) 1999, 2010, Oracle. All rights reserved. -->
<!-- Do not modify the contents of this file by hand. -->
<INVENTORY>
<VERSION_INFO>
   <SAVED_WITH>11.2.0.2.0</SAVED_WITH>
   <MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>
</VERSION_INFO>
<HOME_LIST>
<HOME NAME="OraDB_11G" LOC="/export/oracle/product/11.2.0.2" TYPE="O" IDX="2">

Once you know the install location

export ORACLE_HOME=full path to install location
export ORACLE_HOME=/export/oracle/product/11.2.0.2
export PATH=$ORACLE_HOME/bin:$PATH

A simple "sqlplus" will give you the version of the client installed.

> sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Fri Mar 23 14:51:09 2012
Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter user-name:

In the above example, the version of Oracle client is 11.2.0.1

In Windows

Registry location variable in windows is INST_LOC

Start > Run > regedit > HKLM > Software > Oracle

Check the Inst_loc entry value which will be the software installed location.

You can use command prompt or you can navigate/explore to the oracle home location and then cd to bin directory to lauch sqlplus which will give you the client version information.

You can use the v$session_connect_info view against the current session ID (SID from the USERENV namespace in SYS_CONTEXT).

e.g.

SELECT
  DISTINCT
  s.client_version
FROM
  v$session_connect_info s
WHERE
  s.sid = SYS_CONTEXT('USERENV', 'SID');

You can get the version of the oracle client by running this command sqlplus /nolog on cmd. Another alternative will be to browse to the path C:\Program Files\Oracle\Inventory and open the "Inventory.xml" file which will give you the version as per below:

<?xml version="1.0" standalone="yes" ?>
<!-- Copyright (c) 1999, 2019, Oracle and/or its affiliates.
All rights reserved. -->
<!-- Do not modify the contents of this file by hand. -->
<INVENTORY>
<VERSION_INFO>
   <SAVED_WITH>12.2.0.1.4</SAVED_WITH>
   <MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>
</VERSION_INFO>

Go to ORACLE_HOME/bin and run 'file sqlplus'. see output below.

64-Bit:- cd /tech/oracle/product/v11/bin

$  file sqlplus

sqlplus: **ELF 64-bit** LSB executable, x86-64, version 1 (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.18, not stripped


32-Bit $ cd /tech/oracle/product/11204_32bit/bin

$ file sqlplus

sqlplus: **ELF 32-bit** LSB executable, Intel 80386, version 1 (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.18, not stripped

This is another, though not necessarily "better", way:

Determining Your Current Version

To determine which Oracle client version you have installed on your pc, run sql*plus to connect to the DW. The folder names may vary somewhat based on your Oracle setup but should be similar. To run sql*plus choose start > programs > Oracle > Oracle - OUDWclient > Application Development > sqlplus. Enter your DW user name, password, and 'ordj' for the host name or service name. This should connect you to the DW via sqlplus. At this point, you could write your own sql statements to pull information from the DW (if you knew sql). The Oracle client version can be determined in the first line - 'SQL*Plus: Release 10.2.0.1.0'.

[Reference] Oracle Client Information http://www.ohio.edu/technology