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 DPA 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 as needed and paste in the attached query.
Mining 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 db.name AS instance_name, ah.actiondate AS timestamp,
ahr.parametername AS tablespace_name,
REPLACE(ahr.levelvalue,',','') AS ts_size_mb
FROM ignite.con_alert a
INNER JOIN ignite.con_alert_history ah on ah.alertid=a.id
INNER JOIN ignite.con_alert_history_results ahr on ahr.historyid=ah.historyid
INNER JOIN ignite.cond db ON db.id = ah.db_id
WHERE a.alertname='Collect Oracle Tablespace Sizes'
AND ah.actiondate >= CURRENT_TIMESTAMP - 14.0
ORDER BY db.name, ah.actiondate, ahr.parametername
