Toad runs this query to make a connect string for RAC. Normally, it returns only one row. In some environments, it may return multiple, and only one of them is the "right" one, because RAC has some listeners that are private (for the interconnect). We need to use only the public ones. The row that we want has a NULL in the NETWORK column.
select distinct ll.inst_id,
sn.value service_name,
ll.value tns
from GV$LISTENER_NETWORK ll,
GV$LISTENER_NETWORK sn,
GV$INSTANCE i
where ll.type = 'LOCAL LISTENER'
and sn.type = 'SERVICE NAME'
and sn.value = SYS_CONTEXT ('USERENV', 'SERVICE_NAME')
and ll.inst_id = sn.inst_id
and ll.inst_id = i.inst_id
and i.instance_name = :iname;
© ALL RIGHTS RESERVED. Feedback Terms of Use Privacy Cookie Preference Center