2d.65. Total Boot-Volume Backup Forecast (By BV) (OCI360_BV_BACKUPS)

#: click on a column heading to sort on it

 Name                                                                                Null?    Type
 ----------------------------------------------------------------------------------- -------- --------------------------------------------------------
 BOOT_VOLUME_ID                                                                               VARCHAR2(1)
 COMPARTMENT_ID                                                                               VARCHAR2(1)
 DISPLAY_NAME                                                                                 VARCHAR2(1)
 EXPIRATION_TIME                                                                              VARCHAR2(1)
 IMAGE_ID                                                                                     VARCHAR2(1)
 LIFECYCLE_STATE                                                                              VARCHAR2(1)
 SIZE_IN_GBS                                                                                  NUMBER(1)
 SOURCE_TYPE                                                                                  VARCHAR2(1)
 TIME_CREATED                                                                                 VARCHAR2(1)
 TIME_REQUEST_RECEIVED                                                                        VARCHAR2(1)
 UNIQUE_SIZE_IN_GBS                                                                           NUMBER(1)
 ID                                                                                           VARCHAR2(1)
 TYPE                                                                                         VARCHAR2(1)


WITH
days_in_future as (
SELECT vdate,
       decode(to_number(to_char (vdate, 'd', 'nls_date_language=english')),1,1,0) is_sunday,
       decode(vdate,trunc(vdate,'MM'),1,0)                                        is_month_first_day,
       decode(vdate,trunc(vdate,'YYYY'),1,0)                                      is_year_first_day
FROM   (SELECT  trunc(sysdate) + rownum vdate
        FROM    dual
        CONNECT BY LEVEL <= 366*6 /*  6 Years */
       )
),
days_in_future_period as (
SELECT vdate,
       case
         when is_sunday+is_month_first_day+is_year_first_day = 0
           then 'ONE_DAY'
         when is_sunday=1 and is_month_first_day+is_year_first_day = 0
           then 'ONE_WEEK'
         when is_month_first_day=1 and is_year_first_day = 0
           then 'ONE_MONTH'
         else 'ONE_YEAR'
         end period
FROM   days_in_future
),
bkp_policies as (
SELECT id policy_id,
       display_name policy_name,
       schedules$period period,
       schedules$backup_type type,
       schedules$retention_seconds retention_seconds
FROM   OCI360_BKP_POLICY
),
bkps_in_future as (
select t2.policy_name,
       t2.policy_id,
       t1.period,
       t2.type,
       t1.vdate creation_date,
       t1.vdate+ (t2.retention_seconds/(60*60*24)) expire_date
FROM   days_in_future_period t1,
       bkp_policies t2
WHERE  t1.period=t2.period
)
,
bkps as (
select jt.DISPLAY_NAME BOOT_VOLUME_NAME,
       jt.IS_HYDRATED,
       jt.SIZE_IN_GBS,
       jt.TIME_CREATED,
       tcomp.NAME COMPARTMENT_NAME,
       jt.LIFECYCLE_STATE,
       jt.VOLUME_GROUP_ID,
       jt.AVAILABILITY_DOMAIN,
       tas.creation_date,
       tas.expire_date,
       jt.ID
from   bkps_in_future tas,
       OCI360_BVOLUMES jt,
       OCI360_COMPARTMENTS tcomp,
       OCI360_BKP_POLICY_ASSIGN tbkppolassign
WHERE  jt.COMPARTMENT_ID = tcomp.ID
AND    jt.ID = tbkppolassign.ASSET_ID (+)
AND    tbkppolassign.POLICY_ID = tas.POLICY_ID (+)
UNION ALL
SELECT jt.DISPLAY_NAME BOOT_VOLUME_NAME,
       jt.IS_HYDRATED,
       jt.SIZE_IN_GBS,
       jt.TIME_CREATED,
       tcomp.NAME COMPARTMENT_NAME,
       jt.LIFECYCLE_STATE,
       jt.VOLUME_GROUP_ID,
       jt.AVAILABILITY_DOMAIN,
       cast(to_timestamp_tz(t1.time_created,'YYYY-MM-DD"T"HH24:MI:SS.FF6TZH:TZM') AT LOCAL AS DATE) creation_date,
       nvl(cast(to_timestamp_tz(t1.expiration_time,'YYYY-MM-DD"T"HH24:MI:SS.FF6TZH:TZM') AT LOCAL AS DATE),sysdate+10000) expire_date,
       jt.ID
FROM   OCI360_BV_BACKUPS t1,
       OCI360_BVOLUMES jt,
       OCI360_COMPARTMENTS tcomp
WHERE  t1.lifecycle_state = 'AVAILABLE'
AND    t1.boot_volume_id = jt.id (+)
AND    t1.compartment_id = tcomp.id
)
select BOOT_VOLUME_NAME,
       IS_HYDRATED,
       SIZE_IN_GBS,
       TIME_CREATED,
       COMPARTMENT_NAME,
       LIFECYCLE_STATE,
       VOLUME_GROUP_ID,
       AVAILABILITY_DOMAIN,
       SUM(CASE WHEN SYSDATE                BETWEEN creation_date AND expire_date THEN 1 ELSE 0 END) TODAY,
       SUM(CASE WHEN ADD_MONTHS(SYSDATE,1)  BETWEEN creation_date AND expire_date THEN 1 ELSE 0 END) NEXT_1MONTH_BKPS,
       SUM(CASE WHEN ADD_MONTHS(SYSDATE,2)  BETWEEN creation_date AND expire_date THEN 1 ELSE 0 END) NEXT_2MONTH_BKPS,
       SUM(CASE WHEN ADD_MONTHS(SYSDATE,3)  BETWEEN creation_date AND expire_date THEN 1 ELSE 0 END) NEXT_3MONTH_BKPS,
       SUM(CASE WHEN ADD_MONTHS(SYSDATE,12) BETWEEN creation_date AND expire_date THEN 1 ELSE 0 END) NEXT_YEAR_BKPS,
       SUM(CASE WHEN ADD_MONTHS(SYSDATE,24) BETWEEN creation_date AND expire_date THEN 1 ELSE 0 END) NEXT_2YEAR_BKPS,
       SUM(CASE WHEN ADD_MONTHS(SYSDATE,60) BETWEEN creation_date AND expire_date THEN 1 ELSE 0 END) NEXT_5YEAR_BKPS,
       ID
from   bkps
GROUP BY BOOT_VOLUME_NAME,
         IS_HYDRATED,
         SIZE_IN_GBS,
         TIME_CREATED,
         COMPARTMENT_NAME,
         LIFECYCLE_STATE,
         VOLUME_GROUP_ID,
         AVAILABILITY_DOMAIN,
         ID;


1 rows selected.