How to list all sessions across all RAC instances in Oracle

GV$ views span every RAC instance. This joins GV$SESSION and GV$PROCESS by instance-id-aware address to list every session across the whole cluster — useful when you don''t know which node a session logged into.

SET LINESIZE 500 PAGESIZE 1000
COL username   FORMAT a15
COL machine    FORMAT a25
COL logon_time FORMAT a20

SELECT NVL(s.username, '(oracle)') AS username,
       s.inst_id,
       s.osuser,
       s.sid,
       s.serial#,
       p.spid,
       s.lockwait,
       s.status,
       s.module,
       s.machine,
       s.program,
       TO_CHAR(s.logon_time, 'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM   gv$session s,
       gv$process p
WHERE  s.paddr   = p.addr
AND    s.inst_id = p.inst_id
ORDER BY s.username, s.osuser;