Description
This alert is used for collection of data only and does not alert or send emails. The data for tablespace sizes is collected and then stored into the Ignite alert tables within the repository. At the bottom of this page is a script that can be used to report on this data for charting.
Alert Definition
To create the alert, click on Alerts > Manage Alerts tab and create a Custom Alert of type Custom SQL Alert - Multiple Numeric Return. Configure the alert similar to below.
Alert Name: Collect Tablespace Sizes
Execution Interval: 1 Day
Notification Text: <None needed>
SQL Statement:
select tablespace_name, sum(bytes)/1024/1024 size_mb
from dba_data_files
group by tablespace_name
union
select tablespace_name, sum(bytes)/1024/1024 size_mb
from dba_temp_files
group by tablespace_name
Execute Against: Monitored Instance
Units: N/A
High Threshold: Min -2, Max -1
Reporting the Results
Note: this query retrieves data collected by this alert for the last 14 days. If the alert name was changed from the default listed above, change the "where a.alertname" part of this query as well.
select ah.dbname instance_name, ah.actiondate timestamp,
ahr.parametername tablespace_name, to_number(replace(ahr.levelvalue,',','')) ts_size_mb
from con_alert a
inner join con_alert_history ah on ah.alertid=a.id
inner join con_alert_history_results ahr on ahr.historyid=ah.historyid
where a.alertname='Collect Tablespace Sizes'
and ah.actiondate >= current_timestamp - 14.0
order by ah.dbname, ah.actiondate, ahr.parametername