2c.60. Boot-Volumes Backup Size Percentile (OCI360_VOLUMES)

#	DISPLAY_NAME	IS_H	SIZE_IN_GBS	VPUS_PER_GB	TIME_CREATED	COMPARTMENT_NAME	LIFECYCLE_STATE	VOLU	AVAILABILITY_DOMAIN	POLICY_N	FULL_UNIQUE_MAX	INCR_UNIQUE_MAX	INCR_UNIQUE_75	INCR_UNIQUE_AVG	ID
1	oci360comp (Boot Volume)	true	47	10	2020-05-20T14:54:10.816000+00:00	compt_dev_app	AVAILABLE		CYtq:US-ASHBURN-AD-3						ocid1.bootvolume.oc1.iad.000000000000000000000000000000000000000000000000000000000204

#: 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)


SELECT jt.DISPLAY_NAME,
       jt.IS_HYDRATED,
       jt.SIZE_IN_GBS,
       jt.VPUS_PER_GB,
       jt.TIME_CREATED,
       tcomp.NAME COMPARTMENT_NAME,
       jt.LIFECYCLE_STATE,
       jt.VOLUME_GROUP_ID,
       jt.AVAILABILITY_DOMAIN,
       tbkppol.POLICY_NAME,
       /*  FULL */
       MAX(tbkp1.unique_size_in_gbs)                                              full_unique_max,
       /*  INCR */
       MAX(tbkp2.unique_size_in_gbs)                                              incr_unique_max,
       PERCENTILE_DISC(0.75)   WITHIN GROUP (ORDER BY tbkp2.unique_size_in_gbs)   incr_unique_75,
       ROUND(AVG(tbkp2.unique_size_in_gbs), 1)                                    incr_unique_avg,
       jt.ID
FROM   OCI360_BVOLUMES jt,
       OCI360_COMPARTMENTS tcomp,
       OCI360_BKP_POLICY_ASSIGN tbkppolassign,
       (select distinct id, display_name policy_name from OCI360_BKP_POLICY) tbkppol,
       OCI360_BV_BACKUPS tbkp1,
       OCI360_BV_BACKUPS tbkp2
WHERE  jt.COMPARTMENT_ID = tcomp.ID
AND    jt.ID = tbkppolassign.ASSET_ID (+)
AND    tbkppolassign.POLICY_ID = tbkppol.ID (+)
AND    jt.ID = tbkp1.boot_volume_id (+)
AND    jt.ID = tbkp2.boot_volume_id (+)
AND    tbkp1.LIFECYCLE_STATE (+) = 'AVAILABLE'
AND    tbkp1.TYPE (+) = 'FULL'
AND    tbkp2.LIFECYCLE_STATE (+) = 'AVAILABLE'
AND    tbkp2.TYPE (+) = 'INCREMENTAL'
GROUP BY jt.DISPLAY_NAME,
         jt.IS_HYDRATED,
         jt.SIZE_IN_GBS,
         jt.VPUS_PER_GB,
         jt.TIME_CREATED,
         tcomp.NAME,
         jt.LIFECYCLE_STATE,
         jt.VOLUME_GROUP_ID,
         jt.AVAILABILITY_DOMAIN,
         tbkppol.POLICY_NAME,
         jt.ID
ORDER  BY COMPARTMENT_NAME, jt.TIME_CREATED DESC;


1 rows selected.