Identify the Application that are connecting non-19c client

Learning_something_new 0
Viewed 30

We have upgraded our database to use Oracle client 19c. There are few applications that are accessing our database to connect to their data. we wanted to know if they have upgraded the oracle client to 19c or still using 12.

I wrote a SQL query to capture the Application users that use non-19c client:

select distinct a.inst_id,a.sid,a.serial#,a.client_version,c.version database_version, A.AUTHENTICATION_TYPE,  a.client_oci_library, a.client_driver, a.osuser, b.username,b.machine
from      gv$session_connect_info a,
gv$session b,
(select distinct version from gv$instance) c
where a.inst_id = B.INST_ID  and to_number(decode(substr(a.client_version,1,2),'Un',0,substr(a.client_version,1,2))) < to_number(substr(c.version,1,2)) < 12
and   a.sid = b.sid
and   a.serial# = b.serial#
and   b.type !='BACKGROUND'
and   a.osuser !='oracle'
and   to_number(decode(substr(a.client_version,1,2),'Un',0,substr(a.client_version,1,2))) < to_number(substr(c.version,1,2))
order by a.inst_id,a.sid,a.serial#;

But I am getting an error as below:

ERROR at line 8:
ORA-00933: SQL command not properly ended

Please suggest what is the better way to capture the non 19c users ?

Goal is to get the username along with the client name the users are currently using

oracle sql
1 Answers

Your first and clause has:

to_number(decode(substr(a.client_version,1,2),'Un',0,substr(a.client_version,1,2))) < to_number(substr(c.version,1,2)) < 12

or in shorter form to see the problem:

to_number(something) < to_number(something) < 12

You can't chain the < comparisons like that. In your final and clause you're just doing:

and   to_number(decode(substr(a.client_version,1,2),'Un',0,substr(a.client_version,1,2))) < to_number(substr(c.version,1,2))

so it isn't clear if they should both be like that (and this duplicates, so one is redundant), or one or both should be:

to_number(decode(substr(a.client_version,1,2),'Un',0,substr(a.client_version,1,2))) < 12

... though even then you probably meant <= 12 if you want to find clients still using 12c. (Or maybe < 19 if you could have 18c clients?)

So that would make the whole thing:

select distinct a.inst_id,a.sid,a.serial#,a.client_version,c.version database_version, A.AUTHENTICATION_TYPE,  a.client_oci_library, a.client_driver, a.osuser, b.username,b.machine
from gv$session_connect_info a,
gv$session b,
(select distinct version from gv$instance) c
where a.inst_id = B.INST_ID 
and   a.sid = b.sid
and   a.serial# = b.serial#
and   b.type !='BACKGROUND'
and   a.osuser !='oracle'
and   to_number(decode(substr(a.client_version,1,2),'Un',0,substr(a.client_version,1,2))) <= 12
order by a.inst_id,a.sid,a.serial#;

or with modern join syntax:

select distinct a.inst_id,a.sid,a.serial#,a.client_version,c.version database_version, A.AUTHENTICATION_TYPE,  a.client_oci_library, a.client_driver, a.osuser, b.username,b.machine
from gv$session_connect_info a
join gv$session b
on  a.inst_id = B.INST_ID 
and a.sid = b.sid
and a.serial# = b.serial#
cross join (select distinct version from gv$instance) c
where   b.type !='BACKGROUND'
and   a.osuser !='oracle'
and   to_number(decode(substr(a.client_version,1,2),'Un',0,substr(a.client_version,1,2))) <= 12
order by a.inst_id,a.sid,a.serial#;

Even then, the subquery to get distinct versions seems odd - if you joined directly to v$instance instead of gv$instance then you wouldn't need the subquery. Or join to that but using the session inst_id:

select distinct a.inst_id,a.sid,a.serial#,a.client_version,c.version database_version, A.AUTHENTICATION_TYPE,  a.client_oci_library, a.client_driver, a.osuser, b.username,b.machine
from gv$session_connect_info a
join gv$session b
on  a.inst_id = B.INST_ID 
and a.sid = b.sid
and a.serial# = b.serial#
join gv$instance c
on  c.inst_id = a.inst_id
where   b.type !='BACKGROUND'
and   a.osuser !='oracle'
and   to_number(decode(substr(a.client_version,1,2),'Un',0,substr(a.client_version,1,2))) <= 12
order by a.inst_id,a.sid,a.serial#;
Alex Poole0
Related