By Franck Pachot

.
Want to know all your listeners – including scan listeners, and the services it listens for? It is possible from the instance, with the – undocumented – view V$LISTENER_NETWORK which is there since 11.2

Let’s look at it from a RAC database:

SQL> select * from GV$LISTENER_NETWORK

And here is the result:

INST_ID NETWORK TYPE VALUE CON_ID
1 LOCAL LISTENER (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.62)(PORT=1521)) 1
1 SERVICE NAME racpdb1.racattack 1
1 SERVICE NAME racpdb2.racattack 1
1 SERVICE NAME swingbench.racattack 1
1 SERVICE NAME SYS$SYS.SCHEDULER$_EVENT_QUEUE.RACPDB1.RACATTACK 1
1 REMOTE LISTENER (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.252)(PORT=1521))) 1
1 REMOTE LISTENER (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.253)(PORT=1521))) 1
1 REMOTE LISTENER (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.251)(PORT=1521))) 1
1 SERVICE NAME RAC.racattack 1
2 SERVICE NAME RAC.racattack 1
2 SERVICE NAME racpdb1.racattack 1
2 SERVICE NAME racpdb2.racattack 1
2 SERVICE NAME swingbench.racattack 1
2 REMOTE LISTENER (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.253)(PORT=1521))) 1
2 REMOTE LISTENER (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.252)(PORT=1521))) 1
2 REMOTE LISTENER (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.251)(PORT=1521))) 1
2 LOCAL LISTENER (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.61)(PORT=1521)) 1

You can see:

  • The local listeners
  • The remote listeners (SCAN listeners) with each IP address
  • The services that are registered to those listeners

Even if the view is not documented, it is a good way to have an overview of your network services for the database you are connected to.