Determine which sessions are attached to an AWs

[ola...@gmail.com]

-------------------------
-- NON RAC -- STANDALONE
--------------------------

set pages 500 lines 110

col username format a12 heading "User"
col sid format 99999 heading "SID"
col serial# format 99999 heading "Serial#"
col aw format a18 heading "AW Name"
col attch format a5 heading "Mode"

select username, sid, serial#, owner||'.'||daw.aw_name aw,
decode(attach_mode, 'READ WRITE', 'RW',
'READ ONLY', 'RO', attach_mode) attch
from dba_aws daw, v$aw_olap vo, v$session vs
where daw.aw_number = vo.aw_number and vo.session_id=vs.sid
order by username, sid, daw.aw_name;

User SID Serial# AW Name Mode
------------ ------ ------- ------------------ -----
SCOTT 121 21254 SYS.AWXML RO
SCOTT 121 21254 SYS.EXPRESS RO
SCOTT 121 21254 SCOTT.SCOTT RW

--------------------------
-- RAC -- CLUSTERS
--------------------------

set pages 500 lines 110

col username format a12 heading "User"
col sid format 99999 heading "SID"
col serial# format 99999 heading "Serial#"
col aw format a18 heading "AW Name"
col attch format a5 heading "Mode"
col inst format a12 heading "Instance"

select username, sid, serial#, owner||'.'||daw.aw_name aw,
gvi.instance_name inst,
decode(attach_mode, 'READ WRITE', 'RW',
'READ ONLY', 'RO', attach_mode) attch
from dba_aws daw, gv$aw_olap gvo , gv$instance gvi, gv$session gvs
where daw.aw_number = gvo.aw_number and gvo.session_id=gvs.sid and
gvo.inst_id=gvi.inst_id and gvs.inst_id=gvi.inst_id
order by username, sid, daw.aw_name;

User SID Serial# AW Name Instance Mode
------------ ------ ------- ------------------ ------------ -----
SCOTT 121 21254 SYS.AWXML olaprac1 RO
SCOTT 121 21254 SYS.EXPRESS olaprac1 RO
SCOTT 121 21254 SCOTT.SCOTT olaprac1 RW
SYS 114 64 SYS.EXPRESS olaprac2 RO