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!