Hey,
So I see you liked the last OEM Report post. But some have asked me for a table with all database space usage something like a summary table for executive view, once the last report gives it by the database, right…
Here it goes:
– I hid the target names for safety, but check on the last column “AVG 6-m Growth”. This is the one you want.
– This obviously needs to be in another Report, once no DB filter is present. So, here is an example of the table and the code:

And let me ask you: Would you like to have this view from the ASM Perspective? Like this?

Great, right?
The codes for this:
NOTE: We got comments on this regarding the sysdate implicit conversion to timestamp. It worked fine to by, but in case you have any issue, you might want to check over this. Thank you for pointing out, Günter Nowak!
#Database Space Usage information in the last 6 months
select CURRENT_ALLOCATEDSPACE.TARGET_NAME,
round(CURRENT_ALLOCATEDSPACE.SIZE_GB,2) as "Current allocated(GB)",
round(CURRENT_USEDSIZE.SIZE_GB,2) as "Current Used (GB)",
round(CURRENT_ALLOCATEDSPACE.SIZE_GB- CURRENT_USEDSIZE.SIZE_GB,2) as "Current Free (GB)",
round(CURRENT_USEDSIZE.SIZE_GB-USED_LAST_MONTH.SIZE_GB,2) as "1-m Growth (GB)",
round(MAX_USED.SIZE_GB,2) as "Max Used 6-m (GB)",
round((CURRENT_USEDSIZE.SIZE_GB- FIRST_USEDSPACE.SIZE_GB)/6,2) as "AVG 6-m Growth "
from
(select T.TARGET_NAME, --FIRST USED SPACE
M.ROLLUP_TIMESTAMP as MONTH_TIMESTAMP,
sum(M.AVERAGE/1024)as SIZE_GB
from MGMT$METRIC_DAILY M,
MGMT$TARGET_TYPE T,
(select T.TARGET_NAME, MIN(ROLLUP_TIMESTAMP) MIN_TIME
from MGMT$METRIC_DAILY M, MGMT$TARGET_TYPE T
where (T.TARGET_TYPE='rac_database' OR
(T.TARGET_TYPE='oracle_database' and T.TYPE_QUALIFIER3 != 'RACINST')) and
M.TARGET_GUID=T.TARGET_GUID and
M.METRIC_GUID=T.METRIC_GUID and
T.METRIC_NAME='tbspAllocation' and
T.METRIC_COLUMN='spaceUsed' and
M.METRIC_NAME=T.METRIC_NAME and
M.METRIC_COLUMN=T.METRIC_COLUMN and
M.ROLLUP_TIMESTAMP >= to_timestamp(trunc((sysdate-180),'MM')) and
M.ROLLUP_TIMESTAMP <= to_timestamp(sysdate) group by T.TARGET_NAME, M.METRIC_COLUMN) MIN_DATE where (T.TARGET_TYPE='rac_database' OR (T.TARGET_TYPE='oracle_database' and T.TYPE_QUALIFIER3 != 'RACINST')) and M.TARGET_GUID=T.TARGET_GUID and M.METRIC_GUID=T.METRIC_GUID and T.METRIC_NAME='tbspAllocation' and T.METRIC_COLUMN='spaceUsed' and M.METRIC_NAME=T.METRIC_NAME and M.METRIC_COLUMN=T.METRIC_COLUMN and T.TARGET_NAME=MIN_DATE.TARGET_NAME AND M.ROLLUP_TIMESTAMP=MIN_DATE.MIN_TIME group by t.target_name,m.rollup_timestamp) FIRST_USEDSPACE, --FIRST USED SPACE (select T.TARGET_NAME, --CURRENT ALLOCATED SPACE M.ROLLUP_TIMESTAMP as MONTH_TIMESTAMP, sum(M.AVERAGE/1024)as SIZE_GB from MGMT$METRIC_DAILY M, MGMT$TARGET_TYPE T, (select T.TARGET_NAME, max(ROLLUP_TIMESTAMP) MAX_TIME from MGMT$METRIC_DAILY M, MGMT$TARGET_TYPE T where (T.TARGET_TYPE='rac_database' OR (T.TARGET_TYPE='oracle_database' and T.TYPE_QUALIFIER3 != 'RACINST')) and M.TARGET_GUID=T.TARGET_GUID and M.METRIC_GUID=T.METRIC_GUID and T.METRIC_NAME='tbspAllocation' and T.METRIC_COLUMN='spaceAllocated' and M.METRIC_NAME=T.METRIC_NAME and M.METRIC_COLUMN=T.METRIC_COLUMN and M.ROLLUP_TIMESTAMP >= to_timestamp(trunc((sysdate-180),'MM')) and
M.ROLLUP_TIMESTAMP <= to_timestamp(sysdate) group by T.TARGET_NAME, M.METRIC_COLUMN) MAX_DATE where (T.TARGET_TYPE='rac_database' OR (T.TARGET_TYPE='oracle_database' and T.TYPE_QUALIFIER3 != 'RACINST')) and M.TARGET_GUID=T.TARGET_GUID and M.METRIC_GUID=T.METRIC_GUID and T.METRIC_NAME='tbspAllocation' and T.METRIC_COLUMN='spaceAllocated' and M.METRIC_NAME=T.METRIC_NAME and M.METRIC_COLUMN=T.METRIC_COLUMN and T.TARGET_NAME=MAX_DATE.TARGET_NAME AND M.ROLLUP_TIMESTAMP=MAX_DATE.MAX_TIME group by t.target_name,m.rollup_timestamp) CURRENT_ALLOCATEDSPACE,--CURRENT ALLOCATED SPACE (select T.TARGET_NAME, --USED SPACE IN LAST MONTH M.ROLLUP_TIMESTAMP as MONTH_TIMESTAMP, sum(M.AVERAGE/1024)as SIZE_GB from MGMT$METRIC_DAILY M, MGMT$TARGET_TYPE T, (select T.TARGET_NAME, MIN(ROLLUP_TIMESTAMP) MIN_TIME from MGMT$METRIC_DAILY M, MGMT$TARGET_TYPE T where (T.TARGET_TYPE='rac_database' OR (T.TARGET_TYPE='oracle_database' and T.TYPE_QUALIFIER3 != 'RACINST')) and M.TARGET_GUID=T.TARGET_GUID and M.METRIC_GUID=T.METRIC_GUID and T.METRIC_NAME='tbspAllocation' and T.METRIC_COLUMN='spaceUsed' and M.METRIC_NAME=T.METRIC_NAME and M.METRIC_COLUMN=T.METRIC_COLUMN and M.ROLLUP_TIMESTAMP >= to_timestamp(trunc((sysdate-30),'MM')) and
M.ROLLUP_TIMESTAMP <= to_timestamp(sysdate) group by T.TARGET_NAME, M.METRIC_COLUMN) MIN_DATE where (T.TARGET_TYPE='rac_database' OR (T.TARGET_TYPE='oracle_database' and T.TYPE_QUALIFIER3 != 'RACINST')) and M.TARGET_GUID=T.TARGET_GUID and M.METRIC_GUID=T.METRIC_GUID and T.METRIC_NAME='tbspAllocation' and T.METRIC_COLUMN='spaceUsed' and M.METRIC_NAME=T.METRIC_NAME and M.METRIC_COLUMN=T.METRIC_COLUMN and T.TARGET_NAME=MIN_DATE.TARGET_NAME AND M.ROLLUP_TIMESTAMP=MIN_DATE.MIN_TIME group by t.target_name,m.rollup_timestamp) USED_LAST_MONTH, --USED SPACE IN LAST MONTH (select A_SIZE.TARGET_NAME, --MAX USED SPACE IN 6 MONTHS round(max(A_SIZE.SIZE_GB),2) as SIZE_GB from (select T.TARGET_NAME, M.ROLLUP_TIMESTAMP as MONTH_TIMESTAMP, sum(M.AVERAGE/1024) as SIZE_GB from MGMT$METRIC_DAILY M, MGMT$TARGET_TYPE T where (T.TARGET_TYPE='rac_database' OR (T.TARGET_TYPE='oracle_database' and T.TYPE_QUALIFIER3 != 'RACINST')) and M.TARGET_GUID=T.TARGET_GUID and M.METRIC_GUID=T.METRIC_GUID and T.METRIC_NAME='tbspAllocation' and T.METRIC_COLUMN='spaceUsed' and M.ROLLUP_TIMESTAMP >= to_timestamp(trunc((sysdate-180),'MM')) and
M.ROLLUP_TIMESTAMP <= to_timestamp(sysdate) group by T.TARGET_NAME, M.METRIC_COLUMN, M.ROLLUP_TIMESTAMP) A_SIZE group by A_SIZE.TARGET_NAME) MAX_USED, --MAX USED SPACE IN 6 MONTHS
(select T.TARGET_NAME, --CURRENT USED SPACE
M.ROLLUP_TIMESTAMP as MONTH_TIMESTAMP, sum(M.AVERAGE/1024)as SIZE_GB from MGMT$METRIC_DAILY M, MGMT$TARGET_TYPE T, (select T.TARGET_NAME, max(ROLLUP_TIMESTAMP) MAX_TIME from MGMT$METRIC_DAILY M, MGMT$TARGET_TYPE T where (T.TARGET_TYPE='rac_database' OR (T.TARGET_TYPE='oracle_database' and T.TYPE_QUALIFIER3 != 'RACINST')) and M.TARGET_GUID=T.TARGET_GUID and M.METRIC_GUID=T.METRIC_GUID and T.METRIC_NAME='tbspAllocation' and T.METRIC_COLUMN='spaceUsed' and M.METRIC_NAME=T.METRIC_NAME and M.METRIC_COLUMN=T.METRIC_COLUMN and M.ROLLUP_TIMESTAMP >= to_timestamp(trunc((sysdate-180),'MM')) and
M.ROLLUP_TIMESTAMP <= to_timestamp(sysdate)
group by T.TARGET_NAME,
M.METRIC_COLUMN) MAX_DATE
where (T.TARGET_TYPE='rac_database' OR
(T.TARGET_TYPE='oracle_database' and T.TYPE_QUALIFIER3 != 'RACINST')) and
M.TARGET_GUID=T.TARGET_GUID and
M.METRIC_GUID=T.METRIC_GUID and
T.METRIC_NAME='tbspAllocation' and
T.METRIC_COLUMN='spaceUsed' and
M.METRIC_NAME=T.METRIC_NAME and
M.METRIC_COLUMN=T.METRIC_COLUMN and
T.TARGET_NAME=MAX_DATE.TARGET_NAME AND M.ROLLUP_TIMESTAMP=MAX_DATE.MAX_TIME
group by t.target_name,m.rollup_timestamp) CURRENT_USEDSIZE
WHERE
CURRENT_ALLOCATEDSPACE.TARGET_NAME=CURRENT_USEDSIZE.TARGET_NAME and
CURRENT_ALLOCATEDSPACE.TARGET_NAME=USED_LAST_MONTH.TARGET_NAME and
CURRENT_ALLOCATEDSPACE.TARGET_NAME=FIRST_USEDSPACE.TARGET_NAME and
CURRENT_ALLOCATEDSPACE.TARGET_NAME=MAX_USED.TARGET_NAME
order by 3 desc
# ASM Diskgroup Usage information in the last 6 months
select CURRENT_ALLOCATEDSPACE.TARGET_NAME "DB Name" ,
CURRENT_ALLOCATEDSPACE.KEY_VALUE "Diskgroup",
round(CURRENT_ALLOCATEDSPACE.SIZE_GB,2) as "Current Allocated (GB)",
round(CURRENT_ALLOCATEDSPACE.SIZE_GB-CURRENT_FREESIZE.SIZE_GB,2) as "Current Used (GB)",
round(CURRENT_FREESIZE.SIZE_GB,2) as "Current Free (GB)",
round((CURRENT_ALLOCATEDSPACE.SIZE_GB-CURRENT_FREESIZE.SIZE_GB)-(TOTAL_SPACE_LAST_MONTH.SIZE_GB-FREE_LAST_MONTH.SIZE_GB),2) as "1-m Growth (GB)",
round(USED_SPACE_INFORMATION.USED_SPACE,2) as "Max Used 6-m (GB)",
round(((CURRENT_ALLOCATEDSPACE.SIZE_GB-CURRENT_FREESIZE.SIZE_GB)- (FIRST_TOTAL_SPACE.SIZE_GB-FIRST_FREESPACE.SIZE_GB))/6,2) as "AVG 6-m Growth"
from
(select T.TARGET_NAME, --FIRST FREE SPACE
M.KEY_VALUE,
M.ROLLUP_TIMESTAMP as MONTH_TIMESTAMP,
sum(M.AVERAGE/1024)as SIZE_GB
from MGMT$METRIC_DAILY M,
MGMT$TARGET_TYPE T,
(select T.TARGET_NAME, M.KEY_VALUE, MIN(ROLLUP_TIMESTAMP) MIN_TIME
from MGMT$METRIC_DAILY M, MGMT$TARGET_TYPE T
where T.TARGET_TYPE='osm_cluster' and
M.TARGET_GUID=T.TARGET_GUID and
M.METRIC_GUID=T.METRIC_GUID and
T.METRIC_NAME='DiskGroup_Usage' and
T.METRIC_COLUMN='free_mb' and
M.METRIC_NAME=T.METRIC_NAME and
M.METRIC_COLUMN=T.METRIC_COLUMN and
M.ROLLUP_TIMESTAMP >= to_timestamp(trunc((sysdate-180),'MM')) and
M.ROLLUP_TIMESTAMP <= to_timestamp(sysdate) group by T.TARGET_NAME,M.key_value,M.METRIC_COLUMN) MIN_DATE where T.TARGET_TYPE='osm_cluster' and M.TARGET_GUID=T.TARGET_GUID and M.METRIC_GUID=T.METRIC_GUID and T.METRIC_NAME='DiskGroup_Usage' and T.METRIC_COLUMN='free_mb' and M.METRIC_NAME=T.METRIC_NAME and M.METRIC_COLUMN=T.METRIC_COLUMN and T.TARGET_NAME=MIN_DATE.TARGET_NAME AND M.KEY_VALUE=MIN_DATE.KEY_VALUE AND M.ROLLUP_TIMESTAMP=MIN_DATE.MIN_TIME group by t.target_name,M.KEY_VALUE, m.rollup_timestamp) FIRST_FREESPACE, --FIRST FREE SPACE (select T.TARGET_NAME, --FIRST TOTAL SPACE M.KEY_VALUE, M.ROLLUP_TIMESTAMP as MONTH_TIMESTAMP, sum(M.AVERAGE/1024)as SIZE_GB from MGMT$METRIC_DAILY M, MGMT$TARGET_TYPE T, (select T.TARGET_NAME, M.KEY_VALUE, MIN(ROLLUP_TIMESTAMP) MIN_TIME from MGMT$METRIC_DAILY M, MGMT$TARGET_TYPE T where T.TARGET_TYPE='osm_cluster' and M.TARGET_GUID=T.TARGET_GUID and M.METRIC_GUID=T.METRIC_GUID and T.METRIC_NAME='DiskGroup_Usage' and T.METRIC_COLUMN='total_mb' and M.METRIC_NAME=T.METRIC_NAME and M.METRIC_COLUMN=T.METRIC_COLUMN and M.ROLLUP_TIMESTAMP >= to_timestamp(trunc((sysdate-180),'MM')) and
M.ROLLUP_TIMESTAMP <= to_timestamp(sysdate) group by T.TARGET_NAME,M.key_value,M.METRIC_COLUMN) MIN_DATE where T.TARGET_TYPE='osm_cluster' and M.TARGET_GUID=T.TARGET_GUID and M.METRIC_GUID=T.METRIC_GUID and T.METRIC_NAME='DiskGroup_Usage' and T.METRIC_COLUMN='total_mb' and M.METRIC_NAME=T.METRIC_NAME and M.METRIC_COLUMN=T.METRIC_COLUMN and T.TARGET_NAME=MIN_DATE.TARGET_NAME AND M.KEY_VALUE=MIN_DATE.KEY_VALUE AND M.ROLLUP_TIMESTAMP=MIN_DATE.MIN_TIME group by t.target_name,M.KEY_VALUE, m.rollup_timestamp) FIRST_TOTAL_SPACE, --FIRST TOTAL SPACE (select T.TARGET_NAME, --CURRENT TOTAL SPACE M.KEY_VALUE, M.ROLLUP_TIMESTAMP as MONTH_TIMESTAMP, sum(M.AVERAGE/1024)as SIZE_GB from MGMT$METRIC_DAILY M, MGMT$TARGET_TYPE T, (select T.TARGET_NAME, M.KEY_VALUE, max(ROLLUP_TIMESTAMP) MAX_TIME from MGMT$METRIC_DAILY M, MGMT$TARGET_TYPE T where T.TARGET_TYPE='osm_cluster' and M.TARGET_GUID=T.TARGET_GUID and M.METRIC_GUID=T.METRIC_GUID and T.METRIC_NAME='DiskGroup_Usage' and T.METRIC_COLUMN='total_mb' and M.METRIC_NAME=T.METRIC_NAME and M.METRIC_COLUMN=T.METRIC_COLUMN and M.ROLLUP_TIMESTAMP >= to_timestamp(trunc((sysdate-180),'MM')) and
M.ROLLUP_TIMESTAMP <= to_timestamp(sysdate) group by T.TARGET_NAME, m.key_value, M.METRIC_COLUMN) MAX_DATE where T.TARGET_TYPE='osm_cluster' and M.TARGET_GUID=T.TARGET_GUID and M.METRIC_GUID=T.METRIC_GUID and T.METRIC_NAME='DiskGroup_Usage' and T.METRIC_COLUMN='total_mb' and M.METRIC_NAME=T.METRIC_NAME and M.METRIC_COLUMN=T.METRIC_COLUMN and T.TARGET_NAME=MAX_DATE.TARGET_NAME AND M.KEY_VALUE=MAX_DATE.KEY_VALUE AND M.ROLLUP_TIMESTAMP=MAX_DATE.MAX_TIME group by t.target_name,m.key_value,m.rollup_timestamp order by 1,3) CURRENT_ALLOCATEDSPACE,--CURRENT TOTAL SPACE (select T.TARGET_NAME, --FREE SPACE IN LAST MONTH M.KEY_VALUE, M.ROLLUP_TIMESTAMP as MONTH_TIMESTAMP, sum(M.AVERAGE/1024)as SIZE_GB from MGMT$METRIC_DAILY M, MGMT$TARGET_TYPE T, (select T.TARGET_NAME,M.KEY_VALUE, MIN(ROLLUP_TIMESTAMP) MIN_TIME from MGMT$METRIC_DAILY M, MGMT$TARGET_TYPE T where T.TARGET_TYPE='osm_cluster' and M.TARGET_GUID=T.TARGET_GUID and M.METRIC_GUID=T.METRIC_GUID and T.METRIC_NAME='DiskGroup_Usage' and T.METRIC_COLUMN='free_mb' and M.METRIC_NAME=T.METRIC_NAME and M.METRIC_COLUMN=T.METRIC_COLUMN and M.ROLLUP_TIMESTAMP >= to_timestamp(trunc((sysdate-30),'MM')) and
M.ROLLUP_TIMESTAMP <= to_timestamp(sysdate) group by T.TARGET_NAME,M.KEY_VALUE) MIN_DATE where T.TARGET_TYPE='osm_cluster' and M.TARGET_GUID=T.TARGET_GUID and M.METRIC_GUID=T.METRIC_GUID and T.METRIC_NAME='DiskGroup_Usage' and T.METRIC_COLUMN='free_mb' and M.METRIC_NAME=T.METRIC_NAME and M.METRIC_COLUMN=T.METRIC_COLUMN and T.TARGET_NAME=MIN_DATE.TARGET_NAME AND M.KEY_VALUE=MIN_DATE.KEY_VALUE and M.ROLLUP_TIMESTAMP=MIN_DATE.MIN_TIME group by t.target_name,m.key_value, m.rollup_timestamp) FREE_LAST_MONTH, --FREE SPACE IN LAST MONTH (select T.TARGET_NAME, --TOTAL SPACE IN LAST MONTH M.KEY_VALUE, M.ROLLUP_TIMESTAMP as MONTH_TIMESTAMP, sum(M.AVERAGE/1024)as SIZE_GB from MGMT$METRIC_DAILY M, MGMT$TARGET_TYPE T, (select T.TARGET_NAME,M.KEY_VALUE, MIN(ROLLUP_TIMESTAMP) MIN_TIME from MGMT$METRIC_DAILY M, MGMT$TARGET_TYPE T where T.TARGET_TYPE='osm_cluster' and M.TARGET_GUID=T.TARGET_GUID and M.METRIC_GUID=T.METRIC_GUID and T.METRIC_NAME='DiskGroup_Usage' and T.METRIC_COLUMN='total_mb' and M.METRIC_NAME=T.METRIC_NAME and M.METRIC_COLUMN=T.METRIC_COLUMN and M.ROLLUP_TIMESTAMP >= to_timestamp(trunc((sysdate-30),'MM')) and
M.ROLLUP_TIMESTAMP <= to_timestamp(sysdate) group by T.TARGET_NAME,M.KEY_VALUE) MIN_DATE where T.TARGET_TYPE='osm_cluster' and M.TARGET_GUID=T.TARGET_GUID and M.METRIC_GUID=T.METRIC_GUID and T.METRIC_NAME='DiskGroup_Usage' and T.METRIC_COLUMN='total_mb' and M.METRIC_NAME=T.METRIC_NAME and M.METRIC_COLUMN=T.METRIC_COLUMN and T.TARGET_NAME=MIN_DATE.TARGET_NAME AND M.KEY_VALUE=MIN_DATE.KEY_VALUE and M.ROLLUP_TIMESTAMP=MIN_DATE.MIN_TIME group by t.target_name,m.key_value, m.rollup_timestamp) TOTAL_SPACE_LAST_MONTH, --TOTAL SPACE IN LAST MONTH (SELECT SPACE_ALLOCATED.TARGET_NAME, -- USED_SPACE_INFORMATION SPACE_ALLOCATED.KEY_VALUE, max(SPACE_ALLOCATED.SIZE_GB- FREE_SPACE.SIZE_GB) USED_SPACE FROM( select T.TARGET_NAME, M.KEY_VALUE, M.ROLLUP_TIMESTAMP as MONTH_TIMESTAMP, sum(M.AVERAGE/1024) as SIZE_GB from MGMT$METRIC_DAILY M, MGMT$TARGET_TYPE T where T.TARGET_TYPE='osm_cluster' and M.TARGET_GUID=T.TARGET_GUID and M.METRIC_GUID=T.METRIC_GUID and T.METRIC_NAME='DiskGroup_Usage' and T.METRIC_COLUMN='total_mb' and M.ROLLUP_TIMESTAMP >= to_timestamp(trunc((sysdate-180),'MM')) and
M.ROLLUP_TIMESTAMP <= to_timestamp(sysdate) group by T.TARGET_NAME, M.KEY_VALUE, M.ROLLUP_TIMESTAMP) SPACE_ALLOCATED, (select T.TARGET_NAME, M.KEY_VALUE, M.ROLLUP_TIMESTAMP as MONTH_TIMESTAMP, sum(M.AVERAGE/1024) as SIZE_GB from MGMT$METRIC_DAILY M, MGMT$TARGET_TYPE T where T.TARGET_TYPE='osm_cluster' and M.TARGET_GUID=T.TARGET_GUID and M.METRIC_GUID=T.METRIC_GUID and T.METRIC_NAME='DiskGroup_Usage' and T.METRIC_COLUMN='free_mb' and M.ROLLUP_TIMESTAMP >= to_timestamp(trunc((sysdate-180),'MM')) and
M.ROLLUP_TIMESTAMP <= to_timestamp(sysdate) group by T.TARGET_NAME, M.KEY_VALUE, M.ROLLUP_TIMESTAMP) FREE_SPACE WHERE SPACE_ALLOCATED.TARGET_NAME=FREE_SPACE.TARGET_NAME AND SPACE_ALLOCATED.KEY_VALUE=FREE_SPACE.KEY_VALUE AND SPACE_ALLOCATED.MONTH_TIMESTAMP=FREE_SPACE.MONTH_TIMESTAMP group by SPACE_ALLOCATED.TARGET_NAME, SPACE_ALLOCATED.KEY_VALUE) USED_SPACE_INFORMATION, -- USED_SPACE_INFORMATION (select T.TARGET_NAME, --CURRENT FREE SPACE M.KEY_VALUE, M.ROLLUP_TIMESTAMP as MONTH_TIMESTAMP, sum(M.AVERAGE/1024)as SIZE_GB from MGMT$METRIC_DAILY M, MGMT$TARGET_TYPE T, (select T.TARGET_NAME, M.KEY_VALUE, max(ROLLUP_TIMESTAMP) MAX_TIME from MGMT$METRIC_DAILY M, MGMT$TARGET_TYPE T where T.TARGET_TYPE='osm_cluster' and M.TARGET_GUID=T.TARGET_GUID and M.METRIC_GUID=T.METRIC_GUID and T.METRIC_NAME='DiskGroup_Usage' and T.METRIC_COLUMN='free_mb' and M.METRIC_NAME=T.METRIC_NAME and M.METRIC_COLUMN=T.METRIC_COLUMN and M.ROLLUP_TIMESTAMP >= to_timestamp(trunc((sysdate-180),'MM')) and
M.ROLLUP_TIMESTAMP <= to_timestamp(sysdate)
group by T.TARGET_NAME,M.KEY_VALUE ,M.METRIC_COLUMN) MAX_DATE
where T.TARGET_TYPE='osm_cluster' and
M.TARGET_GUID=T.TARGET_GUID and
M.METRIC_GUID=T.METRIC_GUID and
T.METRIC_NAME='DiskGroup_Usage' and
T.METRIC_COLUMN='free_mb' and
M.METRIC_NAME=T.METRIC_NAME and
M.METRIC_COLUMN=T.METRIC_COLUMN and
T.TARGET_NAME=MAX_DATE.TARGET_NAME AND M.KEY_VALUE=MAX_DATE.KEY_VALUE AND M.ROLLUP_TIMESTAMP=MAX_DATE.MAX_TIME
group by t.target_name,m.key_value,m.rollup_timestamp) CURRENT_FREESIZE --CURRENT FREE SPACE
WHERE
(CURRENT_ALLOCATEDSPACE.TARGET_NAME=CURRENT_FREESIZE.TARGET_NAME and CURRENT_ALLOCATEDSPACE.KEY_VALUE=CURRENT_FREESIZE.KEY_VALUE) and
(CURRENT_ALLOCATEDSPACE.TARGET_NAME=FREE_LAST_MONTH.TARGET_NAME and CURRENT_ALLOCATEDSPACE.KEY_VALUE=FREE_LAST_MONTH.KEY_VALUE) and
(CURRENT_ALLOCATEDSPACE.TARGET_NAME=FIRST_FREESPACE.TARGET_NAME and CURRENT_ALLOCATEDSPACE.KEY_VALUE=FIRST_FREESPACE.KEY_VALUE) and
(CURRENT_ALLOCATEDSPACE.TARGET_NAME=TOTAL_SPACE_LAST_MONTH.TARGET_NAME and CURRENT_ALLOCATEDSPACE.KEY_VALUE=TOTAL_SPACE_LAST_MONTH.KEY_VALUE) and
(CURRENT_ALLOCATEDSPACE.TARGET_NAME=FIRST_TOTAL_SPACE.TARGET_NAME and CURRENT_ALLOCATEDSPACE.KEY_VALUE=FIRST_TOTAL_SPACE.KEY_VALUE) and
(CURRENT_ALLOCATEDSPACE.TARGET_NAME=USED_SPACE_INFORMATION.TARGET_NAME AND CURRENT_ALLOCATEDSPACE.KEY_VALUE=USED_SPACE_INFORMATION.KEY_VALUE )
order by 4 desc