3a.1.101. VCN Map (OCI360_VCNS)

 Name                                                                                Null?    Type
 ----------------------------------------------------------------------------------- -------- --------------------------------------------------------
 ID                                                                                           VARCHAR2(128)
 DNS_LABEL                                                                                    VARCHAR2(8)
 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)
 IPV6_CIDR_BLOCK                                                                              VARCHAR2(4)
 LIFECYCLE_STATE                                                                              VARCHAR2(16)
 VCN_DOMAIN_NAME                                                                              VARCHAR2(32)
 DEFAULT_ROUTE_TABLE_ID                                                                       VARCHAR2(128)
 IPV6_PUBLIC_CIDR_BLOCK                                                                       VARCHAR2(4)
 DEFAULT_DHCP_OPTIONS_ID                                                                      VARCHAR2(128)
 DEFAULT_SECURITY_LIST_ID                                                                     VARCHAR2(128)


  WITH t_regs  AS (SELECT /*+ materialize */
                         region_name,
                         lower(region_key) region_key
                   from  OCI360_REGIONS_SUBS
                   where lower(region_key)='iad'),
       t_vcns  AS (SELECT /*+ materialize */
                         id,
                         display_name || ' - ' || CIDR_BLOCK display_name,
                         substr(id,instr(id,'.',1,3)+1,instr(id,'.',1,4)-instr(id,'.',1,3)-1) region_key
                   FROM  OCI360_VCNS
                   where substr(id,instr(id,'.',1,3)+1,instr(id,'.',1,4)-instr(id,'.',1,3)-1) = 'iad'),
       t_subs  AS (SELECT /*+ materialize */ distinct
                         id,
                         display_name || ' - ' || CIDR_BLOCK display_name,
                         vcn_id
                   FROM  OCI360_SUBNETS
                   where substr(id,instr(id,'.',1,3)+1,instr(id,'.',1,4)-instr(id,'.',1,3)-1) = 'iad'),
       t_insts AS (SELECT t1.id,
                          t3.id subnet_id,
                          t1.display_name,
                          substr(t1.shape,instr(t1.shape,'.',-1)+1) value
                   FROM  OCI360_INSTANCES t1, OCI360_VNIC_ATTACHS t2, t_subs t3
                   WHERE t1.id = t2.instance_id
                   AND   t2.subnet_id = t3.id)
  SELECT region_key "ID", null "PARENT_ID", region_name "DISPLAY_NAME", 0 "VALUE"
  FROM   t_regs
  UNION ALL
  SELECT id, region_key, display_name, 1
  FROM   t_vcns
  UNION ALL
  SELECT id, vcn_id, display_name, 2
  FROM   t_subs
  UNION ALL
  SELECT id, subnet_id, display_name, 3*to_number(value)
  FROM   t_insts
  ;


22 rows selected.