# | 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 | test_vol | true | 50 | 10 | 2020-06-26T19:28:23.433000+00:00 | compt_dev_app | AVAILABLE | CYtq:US-ASHBURN-AD-3 | 10 | 10 | 10 | ocid1.volume.oc1.iad.000000000000000000000000000000000000000000000000000000000710 | |||
2 | oci360_u01 | true | 50 | 10 | 2020-05-20T15:05:40.997000+00:00 | compt_dev_app | AVAILABLE | CYtq:US-ASHBURN-AD-3 | 12 | 1 | 1 | 1 | ocid1.volume.oc1.iad.000000000000000000000000000000000000000000000000000000000712 | ||
3 | test_vol | true | 50 | 0 | 2020-06-26T18:15:04.901000+00:00 | compt_dev_db | TERMINATED | CYtq:US-ASHBURN-AD-3 | ocid1.volume.oc1.iad.000000000000000000000000000000000000000000000000000000000711 |
#: 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_VOLUMES jt,
OCI360_COMPARTMENTS tcomp,
OCI360_BKP_POLICY_ASSIGN tbkppolassign,
(select distinct id, display_name policy_name from OCI360_BKP_POLICY) tbkppol,
OCI360_BACKUPS tbkp1,
OCI360_BACKUPS tbkp2
WHERE jt.COMPARTMENT_ID = tcomp.ID
AND jt.ID = tbkppolassign.ASSET_ID (+)
AND tbkppolassign.POLICY_ID = tbkppol.ID (+)
AND jt.ID = tbkp1.volume_id (+)
AND jt.ID = tbkp2.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;
3 rows selected.