Description
This alert will collect the size of the entire Oracle database instance. This example alert is used only for collecting data and would never alert anyone. This data is provided as input for another custom alert named "Oracle Database Percent Growth". Also shown below is a query that will allow you to report on this data as collect in the Ignite alert tables.
Alert Definition
To create this alert, click on Alerts > Manage Alerts tab and create a Custom Alert of type Custom SQL Alert - Single Numeric Return making it look similar to this example.
Alert Name: Collect Database Size - Oracle
Execution Interval: 1 Day
Notification Text: <None required>
Available Instances: Only choose Oracle instances as this alert will only work against Oracle instances.
SQL Statement:
SELECT sum(size_mb) total_size_mb FROM (
SELECT sum(bytes)/1024/1024 size_mb FROM dba_data_files
UNION
SELECT sum(bytes)/1024/1024 size_mb FROM dba_temp_files)
Execute Against: Monitored Database
Units: Empty
High Threshold: Min 1, Max empty
Reporting from This Data
The data this alert collects is stored in the Ignite alert tables and can be retrieved running a query similar to this.
SELECT ah.dbname, ah.actiondate timestamp, ah.levelvalue db_size_mb
FROM con_alert a
INNER JOIN con_alert_history ah ON ah.alertid=a.id
WHERE a.alertname = 'Collect Database Size - Oracle'
AND ah.actiondate >= current_timestamp - 14.0
order by ah.dbname, ah.actiondate desc