2b.51. Total Volume Backups Forecast (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 distinct
       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 tas.creation_date,
       tas.expire_date,
       tas.type
from   bkps_in_future tas,
       OCI360_VOLUMES jt,
       OCI360_BKP_POLICY_ASSIGN tbkppolassign
WHERE  jt.ID = tbkppolassign.ASSET_ID
AND    tbkppolassign.POLICY_ID = tas.POLICY_ID
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,
       t1.type
FROM   OCI360_BACKUPS t1
WHERE  t1.lifecycle_state = 'AVAILABLE'
),
bkps_sum as (
select vord,
       vdate,
       type,
       SUM(CASE WHEN vdate BETWEEN creation_date AND expire_date THEN 1 ELSE 0 END) TOTAL
from   bkps,
       ( SELECT  rownum vord, 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 vord, vdate, type
)
select
  vord             snap_id,
  TO_CHAR(vdate,  'YYYY-MM-DD HH24:MI:SS') begin_time,
  TO_CHAR(vdate+1,'YYYY-MM-DD HH24:MI:SS') end_time,
  SUM(total)       line1,
  SUM(CASE WHEN type = 'INCREMENTAL' THEN total ELSE 0 END)     line2,
  SUM(CASE WHEN type = 'FULL'        THEN total ELSE 0 END)     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 bkps_sum
group by vord, vdate
order by vord asc;


220 rows selected.