2d.67. Total BV Backups Size Forecast - Avg (OCI360_BACKUPS)

 Name                                                                                Null?    Type
 ----------------------------------------------------------------------------------- -------- --------------------------------------------------------
 ID                                                                                           VARCHAR2(128)
 TYPE                                                                                         VARCHAR2(16)
 VOLUME_ID                                                                                    VARCHAR2(128)
 KMS_KEY_ID                                                                                   VARCHAR2(4)
 SIZE_IN_GBS                                                                                  NUMBER
 SIZE_IN_MBS                                                                                  NUMBER
 SOURCE_TYPE                                                                                  VARCHAR2(8)
 SYSTEM_TAGS$ORCL_CLOUD$FREE_TIER_RETAINED                                                    VARCHAR2(4)
 DEFINED_TAGS$ORACLE_TAGS$CREATEDBY                                                           VARCHAR2(64)
 DEFINED_TAGS$ORACLE_TAGS$CREATEDON                                                           VARCHAR2(32)
 DISPLAY_NAME                                                                                 VARCHAR2(16)
 TIME_CREATED                                                                                 VARCHAR2(32)
 FREEFORM_TAGS$BACKUP                                                                         VARCHAR2(16)
 COMPARTMENT_ID                                                                               VARCHAR2(128)
 EXPIRATION_TIME                                                                              VARCHAR2(4)
 LIFECYCLE_STATE                                                                              VARCHAR2(16)
 UNIQUE_SIZE_IN_GBS                                                                           NUMBER
 UNIQUE_SIZE_IN_MBS                                                                           NUMBER
 TIME_REQUEST_RECEIVED                                                                        VARCHAR2(32)
 SOURCE_VOLUME_BACKUP_ID                                                                      VARCHAR2(4)


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
)
,
bkp_size_estimation as (
select   DECODE(type,'INCREMENTAL',avg(unique_size_in_gbs),'FULL',max(unique_size_in_gbs)) est_unique_size_in_gbs,
         type,
         boot_volume_id
FROM     OCI360_BV_BACKUPS
WHERE    lifecycle_state = 'AVAILABLE'
GROUP BY type,
         boot_volume_id
),
bkps as (
select tas.creation_date,
       tas.expire_date,
       bse.est_unique_size_in_gbs unique_size_in_gbs,
       tas.type
from   bkps_in_future tas,
       OCI360_BVOLUMES jt,
       OCI360_BKP_POLICY_ASSIGN tbkppolassign,
       bkp_size_estimation bse
WHERE  jt.ID = tbkppolassign.ASSET_ID
AND    tbkppolassign.POLICY_ID = tas.POLICY_ID
AND    jt.ID = bse.boot_volume_id (+)
AND    tas.type = bse.type (+)
UNION ALL
SELECT 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,
       unique_size_in_gbs,
       t1.type
FROM   OCI360_BV_BACKUPS t1
WHERE  t1.lifecycle_state = 'AVAILABLE'
),
bkps_sum_day as (
select vdate,
       type,
       SUM(CASE WHEN vdate BETWEEN creation_date AND expire_date THEN unique_size_in_gbs ELSE 0 END) TOTAL
from   bkps,
       ( SELECT  trunc(sysdate) + ((ROWNUM-1)*10) vdate /*  (10 in 10 days) */
         FROM    dual
         CONNECT BY LEVEL <= (366*6) /*  6 Years */
       ) days
where  vdate <= ADD_MONTHS(sysdate,12*6) /*  6 Years */
group by vdate, type
),
bkps_sum_month as (
select   trunc(vdate,'mm') vdate,
         type,
         max(total) total
from     bkps_sum_day
group by trunc(vdate,'mm'), type
),
result as (
select rank() over (order by vdate asc)                                snap_id,
       TO_CHAR(vdate, 'YYYY-MM-DD HH24:MI:SS')                         begin_time,
       TO_CHAR(ADD_MONTHS(vdate,1),'YYYY-MM-DD HH24:MI:SS')            end_time,
       CEIL(SUM(total))                                                line1,
       CEIL(SUM(CASE WHEN type = 'INCREMENTAL' THEN total ELSE 0 END)) line2,
       CEIL(SUM(CASE WHEN type = 'FULL'        THEN total ELSE 0 END)) line3
from   bkps_sum_month
group  by vdate
)
select snap_id,
       begin_time,
       end_time,
       line1,
       line2,
       line3,
       0 dummy_04,
       0 dummy_05,
       0 dummy_06,
       0 dummy_07,
       0 dummy_08,
       0 dummy_09,
       0 dummy_10,
       0 dummy_11,
       0 dummy_12,
       0 dummy_13,
       0 dummy_14,
       0 dummy_15
from   result
order by snap_id;


0 rows selected.