4g.172. Object: ocid1.dbsystem.oc1.iad.000000000000000000000000000000000000000000000000000000000231 Res: PIC_BLOCK_STORAGE_STANDARD between 2020-05-29 and 2020-06-28 (OCI360_REPORTS_USAGE)
Notes:
1) drag to zoom, and right click to reset
2) up to 31 days of awr history were considered
3)
Name Null? Type
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
lineItem/referenceNo VARCHAR2(4000)
lineItem/tenantId VARCHAR2(4000)
lineItem/intervalUsageStart VARCHAR2(4000)
lineItem/intervalUsageEnd VARCHAR2(4000)
product/service VARCHAR2(4000)
product/resource VARCHAR2(4000)
product/compartmentId VARCHAR2(4000)
product/compartmentName VARCHAR2(4000)
product/region VARCHAR2(4000)
product/availabilityDomain VARCHAR2(4000)
product/resourceId VARCHAR2(4000)
usage/consumedQuantity VARCHAR2(4000)
usage/billedQuantity VARCHAR2(4000)
usage/consumedQuantityUnits VARCHAR2(4000)
usage/consumedQuantityMeasure VARCHAR2(4000)
lineItem/isCorrection VARCHAR2(4000)
lineItem/backreferenceNo VARCHAR2(4000)
tags/Oracle-Tags.CreatedBy VARCHAR2(4000)
tags/Oracle-Tags.CreatedOn VARCHAR2(4000)
tags/orcl-cloud.free-tier-retained VARCHAR2(4000)
tags/backup VARCHAR2(4000)
WITH t1 AS (
SELECT SUM("usage/consumedQuantity") CONSUMED_AMOUNT,
SUM("usage/billedQuantity") BILLED_AMOUNT,
TO_TIMESTAMP("lineItem/intervalUsageEnd",'YYYY-MM-DD"T"HH24:MI"Z"') ENDTIMEUTC
FROM OCI360_REPORTS_USAGE
WHERE "product/resource" = 'PIC_BLOCK_STORAGE_STANDARD' AND "product/resourceId" = 'ocid1.dbsystem.oc1.iad.000000000000000000000000000000000000000000000000000000000231'
GROUP BY TO_TIMESTAMP("lineItem/intervalUsageEnd",'YYYY-MM-DD"T"HH24:MI"Z"')
),
trange as (
select trunc(min(TO_TIMESTAMP("lineItem/intervalUsageEnd",'YYYY-MM-DD"T"HH24:MI"Z"')),'HH24') min,
trunc(max(TO_TIMESTAMP("lineItem/intervalUsageEnd",'YYYY-MM-DD"T"HH24:MI"Z"')),'HH24') max
FROM OCI360_REPORTS_USAGE
),
allhours as ( /* Will generate all hours between Min and Max Start Time */
SELECT trange.min + (rownum - 1)/24 vdate,
rownum seq
FROM trange
WHERE trange.min + (rownum - 1)/24 <= trange.max - 1/24 /* Skip last entry as may be incomplete. */
CONNECT BY LEVEL <= (trange.max - trange.min)*24 + 1
)
select seq snap_id,
TO_CHAR(vdate, 'YYYY-MM-DD HH24:MI') begin_time,
TO_CHAR(vdate+1/24,'YYYY-MM-DD HH24:MI') end_time,
TO_CHAR(NVL(SUM(CONSUMED_AMOUNT),0)) line1,
TO_CHAR(NVL(SUM(BILLED_AMOUNT),0)) line2,
0 dummy_03,
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 t1, allhours
where ENDTIMEUTC(+) >= vdate and ENDTIMEUTC(+) < vdate+1/24
group by seq, vdate
order by seq;
718 rows selected.
oci360 (c) 2020, All rights reserved. oci360 v20.06 (2020-06-25) based on moat369 v2002 (2020-02-27). Timestamp: 2020-06-30T15:59:41