Hey,
So I see you liked the last OEM Report post. But some have asked me for a table with all databases space usage something like a summary table for executive view, once the last report give it by database, right…
Here it goes:
– I hided 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 exame of 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:
#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
Guys, the script works fine against repository database using sysman. Please do abit of work and remove the comments(–)
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
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?
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!
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.
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.