set define on;
set ver off;

column NAME format a30
column FAILOVER_METHOD format a20
column CLB_GOAL format a10
column INSTANCIAS format a10

@nls

select name,value from v$parameter where name = 'db_domain';

SELECT DBAS.NAME_HASH SRV_HASH, DBAS.NAME, DBAS.CREATION_DATE, DBAS.FAILOVER_METHOD, DBAS.GOAL, DBAS.CLB_GOAL, count(vas.inst_id) NUM_INST,listagg(vas.inst_id,',') within group (order by inst_id) AS INSTANCIAS
FROM DBA_SERVICES DBAS, GV$ACTIVE_SERVICES VAS
WHERE DBAS.NAME_HASH = VAS.NAME_HASH (+)
AND upper(DBAS.NAME) LIKE upper('%&1%')
GROUP BY DBAS.NAME_HASH, DBAS.NAME, DBAS.CREATION_DATE, DBAS.FAILOVER_METHOD, DBAS.GOAL, DBAS.CLB_GOAL
ORDER BY DBAS.NAME, DBAS.CREATION_DATE, DBAS.FAILOVER_METHOD, DBAS.GOAL, DBAS.CLB_GOAL;

undefine 1

