|
|
This was first published on https://blog.dbi-services.com/list-listeners-and-services-from-the-instance (2015-05-18)
Republishing here for new followers. The content is related to the the versions available at the publication date
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:
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.