Get Active Services

set lines 100 pages 0 trims on feedb off termout off
col name for a20
col inst_list for a32
spool /tmp/srv_active.lst
select trim(name)||':'||trim(substr(inst_lists,2)) inst_list
from (
select name,
          max(sys_connect_by_path(inst_id, ',' )) inst_lists
     from (select name, inst_id,
                    row_number() over
                   (partition by name order by inst_id) rn
              from gv$active_services
-- just pick up application defined services and not SYS,WORLD ot SYS services
              where substr(name,1,instr(name,'_'))=(select name||'_' from v$database)
               )
    start with rn = 1
    connect by prior rn = rn-1 and prior name = name
    group by name
    order by name
)
/
spool off
exit