Determine versions of connected Oracle clients

Last week I was trying to find a way to determine the version of the Oracle clients that are connected/connecting to databases from these databases. We cannot connect to each application server to figure out the used version and certainly not all laptops and desktops from which some users can connect.

The v$session_connect_info view contains a column named client_version so I was hopefull this would provide me with this information, but this info turns out to be VERY unreliable (if present). So I posted the question on the My Oracle Support communities and got a very usefull answer from BobB and MarkDPowell (some very active and bright users on these communities!) comming up with a table called SYS.X$KSUSECON and some decoding on a column (ksuseclvsn) that contains the client version codes as a number.
Using this table you can find the version of connected clients (tested from 11.1.0.7 and higher). Pre 11.1 connected Oracle clients will have a version number of 0 and won’t show up in the query listed next.

The SYS.X$KSUSECON table can’t be queried directly by “normal” users, so first create a view on this table and grant the SELECT privilege on this table to every user you want to be able to query this.

CREATE VIEW xksusecon AS SELECT * FROM SYS.x$ksusecon;

WITH x AS
 (SELECT DISTINCT ksusenum sid,ksuseclvsn,TRIM(TO_CHAR(ksuseclvsn,'xxxxxxxxxxxxxx')) to_c,
   TO_CHAR(ksuseclvsn,'xxxxxxxxxxxxxx') v
  FROM
    sys.xksusecon
 )
 SELECT x.sid,
   DECODE(to_c,'0','Unknown',TO_NUMBER(SUBSTR(v,8,2),'xx') || '.' ||  -- maj_rel
             SUBSTR(v,10,1)      || '.' ||  -- mnt_rel
             SUBSTR(v,11,2)      || '.' ||  -- ias_rel
             SUBSTR(v,13,1)      || '.' ||  -- ptc_set
             SUBSTR(v,14,2)) client_version,  -- port_mnt
   username,program, module
 FROM x, v$session s
 WHERE x.sid like s.sid AND type != 'BACKGROUND'
/

Again, many, many thanks to BobB and MarkDPowell for providing this (in my opinion) VERY usefull information!

This entry was posted in Database and tagged , , , , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published.

Blue Captcha Image
Refresh

*