2a.33. Total Intance Volumes (OCI360_VOLUMES)

#: click on a column heading to sort on it

 Name                                                                                Null?    Type
 ----------------------------------------------------------------------------------- -------- --------------------------------------------------------
 ID                                                                                           VARCHAR2(128)
 KMS_KEY_ID                                                                                   VARCHAR2(4)
 IS_HYDRATED                                                                                  VARCHAR2(4)
 SIZE_IN_GBS                                                                                  NUMBER
 SIZE_IN_MBS                                                                                  NUMBER
 SYSTEM_TAGS$ORCL_CLOUD$FREE_TIER_RETAINED                                                    VARCHAR2(4)
 VPUS_PER_GB                                                                                  NUMBER
 DEFINED_TAGS$ORACLE_TAGS$CREATEDBY                                                           VARCHAR2(64)
 DEFINED_TAGS$ORACLE_TAGS$CREATEDON                                                           VARCHAR2(32)
 DISPLAY_NAME                                                                                 VARCHAR2(16)
 TIME_CREATED                                                                                 VARCHAR2(32)
 COMPARTMENT_ID                                                                               VARCHAR2(128)
 LIFECYCLE_STATE                                                                              VARCHAR2(16)
 VOLUME_GROUP_ID                                                                              VARCHAR2(4)
 AVAILABILITY_DOMAIN                                                                          VARCHAR2(32)
 SOURCE_DETAILS$ID                                                                            VARCHAR2(1)
 SOURCE_DETAILS$TYPE                                                                          VARCHAR2(1)


WITH t1 AS (SELECT /*+ materialize */ * FROM oci360_instances),
     t2 AS (SELECT /*+ materialize */ * FROM oci360_vol_attachs),
     t3 AS (SELECT /*+ materialize */ * FROM oci360_volumes),
     t4 AS (SELECT /*+ materialize */ * FROM oci360_bv_attachs),
     t5 AS (SELECT /*+ materialize */ * FROM oci360_bvolumes)
SELECT t1.display_name                      INSTANCE_NAME,
       COUNT(*)                             TOTAL_VOLS,
       TO_NUMBER(t5.size_in_gbs)            BOOTVOL_SIZE_GBS,
       SUM(nvl(t3.size_in_gbs,0))                  VOL_SIZE_GBS,
       t5.size_in_gbs + SUM(nvl(t3.size_in_gbs,0)) TOTAL_SIZE_GBS,
       t1.id                                INSTANCE_ID
FROM   t1, t2, t3, t4, t5
WHERE  t1.id = t2.instance_id(+)
AND    t2.volume_id = t3.id(+)
AND    t2.lifecycle_state(+) = 'ATTACHED'
AND    t1.id = t4.instance_id
AND    t4.boot_volume_id = t5.id
GROUP  BY t1.id, t1.display_name, t5.size_in_gbs
ORDER  BY total_size_gbs DESC;


1 rows selected.