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
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;
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!
same issue for database report
[Error] Execution (26: 32): ORA-00933: SQL command not properly ended
can we have the scripts in GitHub or something ?
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 ??
Can someone please mail me these scripts in correct format on 1201dimple@gmail.com
still same issue with formatting. Doesn’t work.
still the same format issue
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!
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.
I figured out the issue…all set now…thank you very much for the two script.
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.
Luis,
Try again, this should be solved by now.
Sorry for the long delay on this.
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.