Monday, 21 June 2010

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.

No comments: