Custom SQL Alerts (Oracle)

On this page:

Alert on Oracle Tablespace Usage

To avoid getting running out of tablespace on a tables you can setup a custom SQL alert in AppDynamics for Databases that would run a SQL query to monitor the tablespace.

You can use SQL similar to the following to return tablespaces where auto_extend is turned off, and the free space % is less than 10%. You can tweak these parameters as required. Note that the logic for this alert is included in the SQL (i.e. the < 10%) therefore you can set a threshold of 0 when you create the Custom SQL alert. If the SQL returns anything, then it will automatically trigger the email notification.

SELECT
df.tablespace_name,
TO_CHAR (fs.s/df.bytes*100, '90.0') AS "Free %",
df.status,
TO_CHAR( df.bytes / (1024 * 1024 * 1024), '0.00' ) AS "Size (GB)",
TO_CHAR( (df.bytes / (1024 * 1024 * 1024)), '0.000' ) AS "Free (GB)",
decode (ae.y,1,'YES','NO') as "auto_extend",
df.file_name
FROM dba_data_files df,
(SELECT file_id,SUM(bytes) s
FROM dba_free_space
GROUP BY file_id) fs,
(SELECT file#, 1 y
FROM sys.filext$
GROUP BY file#) ae
WHERE df.file_id = fs.file_id
AND ae.file#(+) = df.file_id
/* Change the following 2 lines as needed */
AND decode (ae.y,1,'YES','NO') = 'NO' /* Only alert on Tablespaces without auto_extend set */
AND ( fs.s/df.bytes*100 ) < 10 /* Free Space percentage */
ORDER BY df.tablespace_name, df.file_id