3a.1.86. Used IPs per Subnets (OCI360_SUBNETS)

#	DISPLAY_NAME	VCN_NAME	COMPARTMENT_NAME	CIDR_BLOCK	TOTAL_AVAILABLE	TOTAL_USED	TOTAL_FREE	DNS_LABEL	SUBNET_DOMAIN_NAME	LIFECYCLE_STATE	VIRTUAL_ROUTER_I	PROHIBIT	ID
1	Public Subnet	vcn-20200428-1946	compt_dev_app	10.0.0.0/24	253	0	253	subnet	subnet.vcn.oraclevcn.com	AVAILABLE	10.0.0.1	false	ocid1.subnet.oc1.iad.000000000000000000000000000000000000000000000000000000000671
2	subnet_dev_db	vcn_dev	compt_ss_net	10.100.6.0/24	253	0	253	subnetdevdb	subnetdevdb.vcndev.oraclevcn.com	AVAILABLE	10.100.6.1	true	ocid1.subnet.oc1.iad.000000000000000000000000000000000000000000000000000000000677
3	subnet_hml_db	vcn_hml	compt_ss_net	10.100.14.0/24	253	0	253	subnethmldb	subnethmldb.vcnhml.oraclevcn.com	AVAILABLE	10.100.14.1	false	ocid1.subnet.oc1.iad.000000000000000000000000000000000000000000000000000000000673
4	subnet_prd_db	vcn_prd	compt_ss_net	10.100.10.0/24	253	0	253	subnetprddb	subnetprddb.vcnprd.oraclevcn.com	AVAILABLE	10.100.10.1	true	ocid1.subnet.oc1.iad.000000000000000000000000000000000000000000000000000000000668
5	subnet_dev_app	vcn_dev	compt_ss_net	10.100.5.0/24	253	0	253	subnetdevapp	subnetdevapp.vcndev.oraclevcn.com	AVAILABLE	10.100.5.1	true	ocid1.subnet.oc1.iad.000000000000000000000000000000000000000000000000000000000672
6	subnet_dev_dmz	vcn_dev	compt_ss_net	10.100.4.0/24	253	1	252	subnetdevdmz	subnetdevdmz.vcndev.oraclevcn.com	AVAILABLE	10.100.4.1	false	ocid1.subnet.oc1.iad.000000000000000000000000000000000000000000000000000000000675
7	subnet_hml_app	vcn_hml	compt_ss_net	10.100.13.0/24	253	0	253	subnethmlapp	subnethmlapp.vcnhml.oraclevcn.com	AVAILABLE	10.100.13.1	true	ocid1.subnet.oc1.iad.000000000000000000000000000000000000000000000000000000000676
8	subnet_hml_dmz	vcn_hml	compt_ss_net	10.100.12.0/24	253	0	253	subnethmldmz	subnethmldmz.vcnhml.oraclevcn.com	AVAILABLE	10.100.12.1	false	ocid1.subnet.oc1.iad.000000000000000000000000000000000000000000000000000000000680
9	subnet_hub_dmz	vcn_hub	compt_ss_net	10.100.0.0/24	253	0	253	subnethubdmz	subnethubdmz.vcnhub.oraclevcn.com	AVAILABLE	10.100.0.1	false	ocid1.subnet.oc1.iad.000000000000000000000000000000000000000000000000000000000678
10	subnet_prd_app	vcn_prd	compt_ss_net	10.100.9.0/24	253	0	253	subnetprdapp	subnetprdapp.vcnprd.oraclevcn.com	AVAILABLE	10.100.9.1	true	ocid1.subnet.oc1.iad.000000000000000000000000000000000000000000000000000000000670
11	subnet_prd_dmz	vcn_prd	compt_ss_net	10.100.8.0/24	253	0	253	subnetprddmz	subnetprddmz.vcnprd.oraclevcn.com	AVAILABLE	10.100.8.1	false	ocid1.subnet.oc1.iad.000000000000000000000000000000000000000000000000000000000679
12	Public Subnet-vcn_test	vcn_test	Test	10.0.0.0/24	253	0	253	sub04182234290	sub04182234290.vcntest.oraclevcn.com	AVAILABLE	10.0.0.1	false	ocid1.subnet.oc1.iad.000000000000000000000000000000000000000000000000000000000674
13	Private Subnet-vcn_test	vcn_test	Test	10.0.1.0/24	253	0	253	sub04182234291	sub04182234291.vcntest.oraclevcn.com	AVAILABLE	10.0.1.1	true	ocid1.subnet.oc1.iad.000000000000000000000000000000000000000000000000000000000669

#: click on a column heading to sort on it

 Name                                                                                Null?    Type
 ----------------------------------------------------------------------------------- -------- --------------------------------------------------------
 ID                                                                                           VARCHAR2(128)
 VCN_ID                                                                                       VARCHAR2(128)
 DNS_LABEL                                                                                    VARCHAR2(16)
 CIDR_BLOCK                                                                                   VARCHAR2(16)
 DEFINED_TAGS$ORACLE_TAGS$CREATEDBY                                                           VARCHAR2(64)
 DEFINED_TAGS$ORACLE_TAGS$CREATEDON                                                           VARCHAR2(32)
 DISPLAY_NAME                                                                                 VARCHAR2(32)
 TIME_CREATED                                                                                 VARCHAR2(32)
 FREEFORM_TAGS$VCN                                                                            VARCHAR2(32)
 COMPARTMENT_ID                                                                               VARCHAR2(128)
 ROUTE_TABLE_ID                                                                               VARCHAR2(128)
 DHCP_OPTIONS_ID                                                                              VARCHAR2(128)
 IPV6_CIDR_BLOCK                                                                              VARCHAR2(4)
 LIFECYCLE_STATE                                                                              VARCHAR2(16)
 VIRTUAL_ROUTER_IP                                                                            VARCHAR2(16)
 SUBNET_DOMAIN_NAME                                                                           VARCHAR2(64)
 VIRTUAL_ROUTER_MAC                                                                           VARCHAR2(32)
 AVAILABILITY_DOMAIN                                                                          VARCHAR2(4)
 IPV6_PUBLIC_CIDR_BLOCK                                                                       VARCHAR2(4)
 IPV6_VIRTUAL_ROUTER_IP                                                                       VARCHAR2(4)
 PROHIBIT_PUBLIC_IP_ON_VNIC                                                                   VARCHAR2(8)
 SECURITY_LIST_IDS                                                                            VARCHAR2(128)


SELECT tsub.display_name,
       tvcn.DISPLAY_NAME VCN_NAME,
       tcomp.NAME COMPARTMENT_NAME,
       tsub.cidr_block,
       power(2,32-substr(tsub.cidr_block,instr(tsub.cidr_block,'/')+1))-3 TOTAL_AVAILABLE,
       count(tpip.id) TOTAL_USED,
       power(2,32-substr(tsub.cidr_block,instr(tsub.cidr_block,'/')+1))-3 - count(tpip.id) TOTAL_FREE,
       tsub.dns_label,
       tsub.subnet_domain_name,
       tsub.lifecycle_state,
       tsub.virtual_router_ip,
       tsub.availability_domain,
       tsub.prohibit_public_ip_on_vnic,
       tsub.id
FROM   (SELECT distinct id, compartment_id, vcn_id, display_name, cidr_block, dns_label, subnet_domain_name, lifecycle_state, virtual_router_ip, availability_domain, prohibit_public_ip_on_vnic FROM OCI360_SUBNETS) tsub,
       OCI360_PRIVATEIPS tpip,
       OCI360_COMPARTMENTS tcomp,
       OCI360_VCNS tvcn
WHERE  substr(tsub.id,instr(tsub.id,'.',1,3)+1,instr(tsub.id,'.',1,4)-instr(tsub.id,'.',1,3)-1) = 'iad'
AND    tsub.compartment_id = tcomp.ID
AND    tsub.id = tpip.SUBNET_ID (+)
AND    tsub.VCN_ID = tvcn.ID
GROUP BY tsub.display_name,
         tsub.cidr_block,
         tsub.dns_label,
         tsub.subnet_domain_name,
         tsub.lifecycle_state,
         tsub.virtual_router_ip,
         tsub.availability_domain,
         tsub.prohibit_public_ip_on_vnic,
         tsub.id,
         tcomp.NAME,
         tvcn.DISPLAY_NAME;


13 rows selected.