Enterprise Manager

Hi All,

Need a little assistance here. I need to produce daily tablespace utilization reports from OEM. So I found the Oracle-provided Monthly report (Oracle Database Tablespace Monthly Space Usage) in the Reports tab of OEM and thought the easiest thing to do would be tu use the "Create Like" functionality to create a daily report. So I took the SQL in the Monthly report, changed it to the following, and ran it in the OEM repository database...

SELECT
TO_DATE(TO_CHAR(a_size.day_timestamp,'DD MON RR'),'DD MON RR') AS CALENDAR_DAY,
round(avg(a_size.size_gb),2) AS SIZE_GB,
round(avg(used.used_gb),2) AS USED_GB,
round(avg(a_size.size_gb - used.used_gb),2) AS FREE_GB,
round(avg((used.used_gb*100)/
decode(a_size.size_gb,0,1,a_size.size_gb))
,2) AS USED_PCT
FROM
(SELECT
m.rollup_timestamp AS day_timestamp,
sum(m.average/1024) AS size_gb
FROM
mgmt$metric_daily m,
mgmt$target_type t
WHERE
t.target_guid=HEXTORAW('B6D664674C45B24DFAF4755556A637ED') AND
(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.rollup_timestamp >= sysdate-60 AND
m.rollup_timestamp <= sysdate
GROUP BY m.metric_column, m.rollup_timestamp) a_size,
(SELECT
m.rollup_timestamp AS day_timestamp,
sum(m.average/1024) AS used_gb
FROM
mgmt$metric_daily m,
mgmt$target_type t
WHERE
t.target_guid=HEXTORAW('B6D664674C45B24DFAF4755556A637ED') AND
(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 >= sysdate-60 AND
m.rollup_timestamp <= sysdate
GROUP BY m.metric_column, m.rollup_timestamp) used
WHERE
a_size.day_timestamp =used.day_timestamp
GROUP BY TO_CHAR(a_size.day_timestamp,'DD MON RR')
ORDER BY CALENDAR_DAY DESC;


This gave me the following results.....

CALENDAR_ SIZE_GB USED_GB FREE_GB USED_PCT

15-DEC-09 861.57 736.85 124.72 85.52
08-DEC-09 861.57 733.51 128.06 85.14
06-DEC-09 861.57 733.53 128.03 85.14
04-DEC-09 801.84 733.88 67.97 91.52
27-NOV-09 801.84 728.51 73.33 90.85
20-NOV-09 792.54 725.44 67.1 91.53
13-NOV-09 792.54 720.59 71.95 90.92
06-NOV-09 792.54 717.12 75.42 90.48
03-NOV-09 792.54 708.21 84.32 89.36
31-OCT-09 792.54 714.4 78.13 90.14
29-OCT-09 792.54 713.48 79.06 90.02
26-OCT-09 792.54 707.64 84.9 89.29
23-OCT-09 792.54 707.37 85.16 89.25
22-OCT-09 792.54 707.3 85.24 89.25
21-OCT-09 792.54 706.06 86.48 89.09


This is almost exactly what I'm looking for except for the fact that it does not appear as if the data is being collected and stored in the repository on a daily basis. It sort of seems to be done on a weekly basis at times but with some random dates in there as well. Does anyone know how to configure the OMS or the agent to collect and store this info daily.


The three components of OEM (the repository DB, the OMS and the agent) are version 10.2.0.4.

Any help would be greatly appreciated.