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

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:

ExecutiveReport.png

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

13 thoughts on “OEM Report: Last 6 month Database Space Usage and Growth [2]

  1. 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

    • 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!

  2. 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.

  3. 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

  4. 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!

Leave a Reply to javeed Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.