Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Is there an easy way to determine to which RAC node of an Oracle 11g R2 system I am connected? I am trying to perform some failover tests and I want to make sure my application is correctly connected to one node and upon the shutdown of this node node makes the transition smoothly to another node without any noticeable delay on the front end. Maybe it is worth mentioning that we make use of TAF.

I considered using Enterprise Manager for this, but I guess that when I am connected to one node running em and this node goes down I will not really have a chance to monitor the nodes connectivity status.

share|improve this question
up vote 4 down vote accepted

For your current session?

select host_name from gv$instance where instance_number=userenv('instance');

For all sessions:

select i.host_name, s.username from 
  gv$session s join
  gv$instance i on (i.inst_id=s.inst_id)
  username is not null;
share|improve this answer
OK, so this shows me how my own session is connected. Is there a chance I can monitor others as well? If I login using sqlplus to check the webapp that logs with a different session? – Stephan Feb 2 '11 at 9:43
USERENV works but Oracle recommends sys_context instead, sys_context('userenv', 'instance'). In 8i: Oracle recommends that you use the application context feature or the SYS_CONTEXT function with the USERENV option.… – Shannon Severance Jun 1 '12 at 1:31
From 9i to 11gR2: USERENV is a legacy function that is retained for backward compatibility. Oracle Corporation recommends that you use the SYS_CONTEXT function with the built-in USERENV namespace.…… – Shannon Severance Jun 1 '12 at 1:32

For current session:

SELECT sys_context('USERENV', 'INSTANCE') AS instance#, sys_context('USERENV', 'INSTANCE_NAME') AS instance_name FROM dual

share|improve this answer
Thanks, that was indeed helpful :) – Stephan Feb 4 '11 at 20:55

If you are connected to node1

SQL> select host_name from v$instance;

If you are connected to nodeN

SQL> select host_name from v$instance;

If you like to know all instances and all nodes

SQL> select instance_name,host_name from gv$instance;


$ crsctl stat res -t
cluster resource
 1 node host1  online
 2 node host2  online
 n node hostn  offline
share|improve this answer
SELECT sid,serial#, inst_id,username,event,ownerid
    WHERE sid=(select sid from v$mystat where rownum=1);
share|improve this answer
A little context for what this query does, or any text besides the query itself would be great... – GregL May 30 at 12:58

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.