Query a RAC Cluster to get Connected users

While testing FCF with JDBC it's often useful to know how many connections I have on each instance within the cluster. Sure it's easy enough to get the instance I am connected to from the JDBC side , but if you run a query as follows using the GV$ tables on the RAC cluster you can get the info as follows for all the connections within your pool.

SQL Query

col username format a10
col service_name format a20
col host_name format a30
col instance_name format a15

select s.username, s.service_name, i.INSTANCE_NAME, i.HOST_NAME
from gv$session s, gv$instance i
where i.INST_ID = s.INST_ID
and s.username = 'SCOTT'
and s.service_name = 'HASERVICE'
/

Note: You will need to replace the username / service name with your details.

Results

  
SQL> @users

USERNAME   SERVICE_NAME         INSTANCE_NAME   HOST_NAME
---------- -------------------- --------------- ------------------------------
SCOTT      HASERVICE            orcl1           apemrac1.au.oracle.com
SCOTT      HASERVICE            orcl1           apemrac1.au.oracle.com
SCOTT      HASERVICE            orcl1           apemrac1.au.oracle.com
SCOTT      HASERVICE            orcl1           apemrac1.au.oracle.com
SCOTT      HASERVICE            orcl2           apemrac2.au.oracle.com
SCOTT      HASERVICE            orcl2           apemrac2.au.oracle.com
SCOTT      HASERVICE            orcl2           apemrac2.au.oracle.com
SCOTT      HASERVICE            orcl2           apemrac2.au.oracle.com
SCOTT      HASERVICE            orcl2           apemrac2.au.oracle.com
SCOTT      HASERVICE            orcl2           apemrac2.au.oracle.com

10 rows selected.