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_NETWORKAnd 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.