2c.59. Boot-Volumes latest backup (OCI360_VOLGROUP_BKP)

#	BOOT_VOLUME_NAME	IS_H	SIZE_IN_GBS	VPUS_PER_GB	TIME_CREATED	COMPARTMENT_NAME	LIFECYCLE_STATE	VOLU	AVAILABILITY_DOMAIN	POLICY_N	LATEST_FULL	LATEST_INCR	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

ERROR:
ORA-04043: object OCI360_VOLGROUP_BKP does not exist



SELECT tb.DISPLAY_NAME BOOT_VOLUME_NAME,
       tb.IS_HYDRATED,
       tb.SIZE_IN_GBS,
       tb.VPUS_PER_GB,
       tb.TIME_CREATED,
       tcomp.NAME COMPARTMENT_NAME,
       tb.LIFECYCLE_STATE,
       tb.VOLUME_GROUP_ID,
       tb.AVAILABILITY_DOMAIN,
       tbkppol.POLICY_NAME,
       max(to_timestamp_tz(tbkp1.time_created,'YYYY-MM-DD"T"HH24:MI:SS.FF6TZH:TZM')) LATEST_FULL,
       max(to_timestamp_tz(tbkp2.time_created,'YYYY-MM-DD"T"HH24:MI:SS.FF6TZH:TZM')) LATEST_INCR,
       tb.ID
FROM   OCI360_BVOLUMES tb,
       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  tb.COMPARTMENT_ID = tcomp.ID
AND    tb.ID = tbkppolassign.ASSET_ID (+)
AND    tbkppolassign.POLICY_ID = tbkppol.ID (+)
AND    tb.ID = tbkp1.boot_volume_id (+)
AND    tb.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 tb.DISPLAY_NAME,
         tb.IS_HYDRATED,
         tb.SIZE_IN_GBS,
         tb.VPUS_PER_GB,
         tb.TIME_CREATED,
         tcomp.NAME,
         tb.LIFECYCLE_STATE,
         tb.VOLUME_GROUP_ID,
         tb.AVAILABILITY_DOMAIN,
         tbkppol.POLICY_NAME,
         tb.ID
ORDER  BY COMPARTMENT_NAME, LATEST_FULL DESC NULLS LAST;


1 rows selected.