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 →

15 Comments

Taushik Dasgupta

11 de September de 2020 at 10:04 3 weeks 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 3 months 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 3 months ago

still same issue with formatting. Doesn’t work.

Reply
emailramakrishnan@gmail.com

1 de June de 2020 at 03:14 4 months ago

still the same format issue

Reply
Günter Nowak

3 de April de 2020 at 13:51 6 months 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 6 months 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 10 months 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 10 months 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 10 months 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 11 months 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 1 year 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 1 year 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 2 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 2 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 6 months ago
    
    Luis,
    Try again, this should be solved by now.
    Sorry for the long delay on this.

Author: matheusdba

15 Comments

Leave a Comment Cancel reply