OEM Report: Last 6 month Database Space Usage and Growth [2]

By matheusdba 19 de March de 2019 3 de April de 2020

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?

ASM-Growth

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

Author: matheusdba

Head Editor at grepOra, Oracle ACE Director ♠ and Database Consultant.

View all posts by matheusdba →

20 Comments

Rich Soule

17 de December de 2021 at 15:59 10 months ago

Thanks so much for this query!

I’m going to post this on my blog, but I substancially rewrote the query to be easier to maintain and read by using CTEs (Common Table Expressions). Performance against our OEM repository went from 84 seconds down to less than 1 second too… A big issue with your query as written is that the target_guid and metric_guid columns are the indexed columns from the mgmt$target_type and mgmt$metric_daily tables, so any time you can use those columns to join results together you’ll end up with a MUCH faster query. While you had those columns you had also added in many other where clauses that also had to be evaluated which dramatically slowed down the query, but didn’t need to be there.

Also, we didn’t have any RAC databases, so I removed that from the database_list CTE, but you could certainly add it back in.

with database_list as (
select unique target_name
, metric_column
, target_guid
, metric_guid
from mgmt$target_type
where target_type in (‘oracle_pdb’, ‘oracle_database’)
and metric_name = ‘tbspAllocation’
and metric_column in (‘spaceUsed’, ‘spaceAllocated’)
), space_used_list as (
select *
from database_list
where metric_column = ‘spaceUsed’
), space_allocated_list as (
select *
from database_list
where metric_column = ‘spaceAllocated’
), last_night_space_used as (
select mgmt$metric_daily.target_name
, target_guid
, round(sum(average / 1024)) as total_current_used_gb
from mgmt$metric_daily
join space_used_list using (target_guid,metric_guid)
where rollup_timestamp = trunc(systimestamp – interval ‘1’ day)
group by mgmt$metric_daily.target_name
, target_guid
), last_night_space_allocated as (
select mgmt$metric_daily.target_name
, target_guid
, round(sum(average / 1024)) as total_current_allocated_gb
from mgmt$metric_daily
join space_allocated_list using (target_guid, metric_guid)
where rollup_timestamp = trunc(systimestamp – interval ‘1’ day)
group by mgmt$metric_daily.target_name
, target_guid
), last_month_space_used as (
select mgmt$metric_daily.target_name
, target_guid
, round(sum(average / 1024)) as total_last_month_used_gb
from mgmt$metric_daily
join space_used_list using (target_guid, metric_guid)
where rollup_timestamp = trunc(add_months(systimestamp, -1))
group by mgmt$metric_daily.target_name
, target_guid
), six_month_ago_space_used as (
select mgmt$metric_daily.target_name
, target_guid
, round(sum(average / 1024)) as total_six_month_ago_used_gb
from mgmt$metric_daily
join space_used_list using (target_guid, metric_guid)
where rollup_timestamp = trunc(add_months(systimestamp, -6))
group by mgmt$metric_daily.target_name
, target_guid
), results as (
select last_night_space_allocated.target_name
, total_current_allocated_gb as current_allocated_gb
, total_current_allocated_gb – total_current_used_gb as current_free_gb
, total_current_used_gb as current_used_gb
, total_last_month_used_gb as last_month_used_gb
, total_six_month_ago_used_gb as six_month_ago_gb
, total_current_used_gb – total_last_month_used_gb as last_month_growth_gb
, total_current_used_gb – total_six_month_ago_used_gb as six_month_growth_gb
, round((total_current_used_gb – total_six_month_ago_used_gb)/6) as average_six_month_growth
from last_night_space_allocated
join last_night_space_used using (target_guid)
join last_month_space_used using (target_guid)
join six_month_ago_space_used using (target_guid)
)
select *
from results
order by last_month_growth_gb desc;

Reply
Brenda Eagleston

1 de July de 2021 at 19:23 1 year ago

I still had to update the scripts by putting — comments on seperate lines, but otherwise it’s good!
I would love to have one of these on tablespaces by DB if it’s something you already did!

Reply
JK

10 de December de 2020 at 07:51 2 years ago

same issue for database report
[Error] Execution (26: 32): ORA-00933: SQL command not properly ended

Reply
samjaz

15 de November de 2020 at 12:54 2 years ago

can we have the scripts in GitHub or something ?

Reply
Taushik Dasgupta

11 de September de 2020 at 10:04 2 years ago

Hi,

Is it possible to add a column to the existing Query (ASM Diskgroup Usage information in the last 6 months) based upon the below calculation?

Free Space in GB/1-m growth in GB ??

Reply
Dimple Patel

13 de July de 2020 at 17:49 2 years ago

Can someone please mail me these scripts in correct format on 1201dimple@gmail.com

Reply
Mateen

29 de June de 2020 at 20:36 2 years ago

still same issue with formatting. Doesn’t work.

Reply
emailramakrishnan@gmail.com

1 de June de 2020 at 03:14 2 years ago

still the same format issue

Reply
Günter Nowak

3 de April de 2020 at 13:51 2 years ago

Hi
This is a great script. Unfortunately the format changed when you published it here. Some line breaks disappeared and so a lot of consecutive lines where joined to a long single lines. This happened several times. This is a problem when one of this lines contains a — comment because the remaining lines that are joined to this line are ignored.

I hope this helps other users:

One can put your statement into an editor an manually insert a line break whenever there are lot of consecutive blanks, e.g. the following part of a line

… (select T.TARGET_NAME, –CURRENT USED SPACE M.ROLLUP_TIMESTAMP as MONTH_TIMESTAMP, …

should be replaced by

…
(select T.TARGET_NAME,
–CURRENT USED SPACE
M.ROLLUP_TIMESTAMP as MONTH_TIMESTAMP,
…

otherwise the — will comment out all the following text and make the statement incorrect.

There is another error in the statement. The call of “to_timestamp(sysdate)” raises a

ORA-01843: not a valid month

in my database. it is not necessary (and not a good idea) to apply to_timestamp on sysdate, which is a date data type. one can remove all to_timestamp function calls in the script.

Finally one should be aware that the tables used in the statement belong to the sysman schema in the Oracle Enterprise Manager repository. So either you log in as SYSMAN user to the repository database or add ‘SYSMAN.’ before each table reference in the script.

regards guenter

Reply
1. matheusdba
  
  3 de April de 2020 at 22:52 2 years ago
  
  Thank you, Günter!
  I just fixed the broken lines.
  
  Regarding the sysdate, this is working fine for me, an implicit conversion should be needed.
  
  Anyways, a note was left regarding this on the post.
  
  Thank you for collaborating!
  
  Reply
raj

19 de December de 2019 at 15:06 3 years ago

Good..just need to remove comments..its work’s fine…Reg “Database Space Usage information in the last 6 months” …I am not able to pull RAC database instance space usage.

Reply
1. raj
  
  19 de December de 2019 at 15:42 3 years ago
  
  I figured out the issue…all set now…thank you very much for the two script.
  
  Reply
DBA

26 de November de 2019 at 13:21 3 years ago

Guys, the script works fine against repository database using sysman. Please do abit of work and remove the comments(–)

Reply
javeed

3 de November de 2019 at 10:28 3 years ago

Hi
In SQL developer it’s giving

First Query
========
ORA-00933: SQL command not properly ended
00933. 00000 – “SQL command not properly ended”
*Cause:
*Action:
Error at Line: 26 Column: 23

Second Query
==========

ORA-00933: SQL command not properly ended
00933. 00000 – “SQL command not properly ended”
*Cause:
*Action:
Error at Line: 27 Column: 23

Reply
Bryan Bu

14 de August de 2019 at 05:38 3 years ago

Hi Sir,
Very useful script but the script not running correctly probably due to format issue. can you also email the script or file via minibu79@gmail.com?

Reply
Raja

18 de May de 2019 at 03:14 3 years ago

The script has clearly lost the correct format when pasted into blog entry, as mentioned by others. if you dont mind, would you email the scripts? Thanks for your help!

Reply
Rizwan kHALID

20 de March de 2019 at 14:56 4 years ago

HI Sir ,
its looks really great , I think was looks for it since long time .
I actually copy and paste this script and executing using sysman on management repository , but its giving some syntenx error , can you please send me sql file via email at rizwankhalid214@hotmail.com

really appreciated.

Reply
1. Luis Santos
  
  26 de March de 2019 at 12:31 4 years ago
  
  I have the same request. The script has clearly lost the correct format when pasted into blog entry.
  
  Please update it direct into the post.
  
  Reply
  1. matheusdba
    
    3 de April de 2020 at 22:52 2 years ago
    
    Luis,
    Try again, this should be solved by now.
    Sorry for the long delay on this.
2. sridhar
  
  1 de March de 2022 at 14:31 7 months ago
  
  HI Sir ,
  its looks really great , I think was looks for it since long time .
  I actually copy and paste this script and executing using sysman on management repository , but its giving some syntenx error , can you please send me sql file via email at sridhar4db@gmail.com.com
  
  really appreciated.
  
  Reply

OEM Report: Last 6 month Database Space Usage and Growth [2]

Author: matheusdba

20 Comments

Leave a Comment Cancel reply

Automation of your purchase to pay process: from invoice to payment

MULTI-CURRENCY BEST PRACTICES IN ORACLE ERP CLOUD AND EBS

Why are Entities so important? Best practice Oracle Digital Assistant Webcast

Master class ¡GRATIS! 📢DevOps: Automatización de despliegues de aplicaciones

EXPERTENSEMINAR: Flows for APEX – prozessorientierte Apps erstellen