OCI query for historical data

gaurav_verma — Wed, 04 Jun 2025 12:54:49 GMT

Hello,

I am looking to build SQL query to see Cluster's Used, Unused and Allocated capacity from past 7 weeks. One pointer for each week.

I am able to generate for current date from view "report_storage_summary_aux" but dont know how to use fact tables.

SELECT storage_name, storage_pool_used_capacity 

FROM report_storage_summary_aux

WHERE storage_name LIKE 'Cluster1' AND storage_pool_used_capacity <> 0  OR

storage_name LIKE 'Cluster2' AND storage_pool_used_capacity <> 0

Thanks

Gaurav

Re: OCI query for historical data

gaurav_verma — Tue, 19 Feb 2019 17:56:24 GMT

For others visibility. This query works for me.

SELECT DISTINCT STORAGE.name, STORAGE.dataCenter AS DC, STORAGE.family, DATEDIM.fullDate AS Date,sum(STORAGE_FACT.CapacityMB) AS CapacityMB, sum(STORAGE_FACT.usedCapacityMB) AS usedCapacityMB 
FROM storage_dimension STORAGE, date_dimension DATEDIM, storage_and_storage_pool_capacity_fact STORAGE_FACT
WHERE 
STORAGE_FACT.storageTk=STORAGE.tk AND 
STORAGE.dataCenter IN('DC1','DC2','DC3') AND 
DATEDIM.tk=STORAGE_FACT.dateTk AND 
DATEDIM.fullDate>(DATE_ADD(CURDATE(),INTERVAL -30 DAY)) AND
GROUP BY STORAGE.name, DATEDIM.fullDate

Thanks

topic Re: OCI query for historical data in General Discussion

OCI query for historical data

Re: OCI query for historical data