Tags

, ,

The deeper you dig, the more nuggets you find in the SYSMAN schema.

This question came up: What are the ipaddresses and subnets for all of our database servers?

Here’s how you can find it:

CREATE OR REPLACE FORCE VIEW scoop.oem_ip_addresses ( host_name, inet_addr, broadcast_addr, subnet, host_aliases ) AS
 SELECT DISTINCT host_name,
     inet_address AS inet_addr,
     broadcast_address AS broadcast_addr,
     SUBSTR ( broadcast_address, 1, ( INSTR ( broadcast_address, '.', -3 ) ) ) || '0' subnet,
     NVL ( host_aliases, host_name ) AS host_aliases
 FROM sysman.mgmt$hw_nic
 WHERE broadcast_address IS NOT NULL
 AND name = 'en0'
 UNION ALL
 SELECT DISTINCT host_name,
     inet_address AS inet_addr,
     broadcast_address AS broadcast_addr,
     SUBSTR ( broadcast_address, 1, ( INSTR ( broadcast_address, '.', -3 ) ) ) || '0' subnet,
     NVL ( host_aliases, host_name ) AS host_aliases
 FROM sysman.mgmt$hw_nic
 WHERE broadcast_address IS NOT NULL
 AND name LIKE 'eth0 %'
 ORDER BY host_name;

CREATE OR REPLACE PUBLIC SYNONYM OEM_IP_ADDRESSES FOR SCOOP.OEM_IP_ADDRESSES;
GRANT SELECT ON SCOOP.OEM_IP_ADDRESSES TO MGMT_VIEW;