Soumya's Database Blog : Batch script to check tablespace usage and send email notification

Issue: We had a requirement for a customer, where oracle 19c database was running on Windows server 2019. Customer wanted to monitor tablespace’s datafile usage and get email alert incase the used space is less than 80%

To mitigate this , we developed a batch script leveraging powershell’s Send-Maillmessage command to achieve this.

Step 1.

First we create the sql which will be used to monitor the tablespace usage. Lets save it as E:\BackupScripts\Tbs_size_check.sql

Set lines 222

SELECT d.tablespace_name "TS Name", d.num_files "Num Files",

d.asize "Size MB", NVL (f.freebytes, 0) "Free MB",

(d.BYTES - NVL (f.freebytes, 0)) "Used MB",

((d.BYTES - NVL (f.freebytes, 0)) * 100) / d.asize "Percent Used"

FROM (SELECT tablespace_name, SUM (BYTES) / 1024 / 1024 freebytes

FROM dba_free_space

GROUP BY tablespace_name) f,

(SELECT tablespace_name, COUNT (DISTINCT (file_id)) num_files,

sum(greatest(maxbytes,bytes))/1024/1024 asize,

SUM (BYTES) / 1024 / 1024 BYTES

FROM dba_data_files

GROUP BY tablespace_name) d

WHERE d.tablespace_name = f.tablespace_name(+)

AND d.tablespace_name not like '%UNDO%'

AND ROUND (((d.BYTES - NVL (f.freebytes, 0)) * 100) / d.asize) > 80;

exit;

Step 2.

Now lets create the batch script that will be used to monitor the tablespace usage and send an alert if the usage hits a predefined threshold value. In our case we have set threshold value to 80%. This can be modified as per the requirement.

@echo on

REM Database credentials

set DB_USER=sys

set DB_PASS=passfordb

set DB_SID=orcl

REM Email configuration

set EMAIL_RECIPIENT=soumya.das@testmail.com,usertwo@testmail.com

set EMAIL_SUBJECT=Tablespace Usage Alert

set FROM_EMAIL=database.alerts@testmail.com

set HOSTNAME=%COMPUTERNAME%

set EMAIL_BODY=Tablespace usage is LOW at %HOSTNAME%. Please take appropriate action.

set ATTACHMENTS=E:\TABLESPACE_USAGE.txt

REM Execute SQL queries using SQL*Plus

REM Execute SQL query and send email if threshold is met

E:\app\oracle\product\19.3.0\dbhome_1\bin\sqlplus -S %DB_USER%/%DB_PASS%@%DB_SID% as sysdba @E:\BackupScripts\TS_SPACE_CHECK1.SQL > E:\TABLESPACE_USAGE.txt

cd E:\

REM Read usage percent and tablespace name from TABLESPACE_USAGE.txt

for /f "tokens=1,6" %%a in (E:\TABLESPACE_USAGE.txt) do (

set tablespace_name=%%a

set usage_percent=%%b

)

rem echo %usage_percent%

REM Check if usage_percent is greater than or equal to the threshold

if %usage_percent% GEQ 80 (

REM Send email alert with tablespace name and usage percentage

powershell -ExecutionPolicy Bypass -Command "Send-MailMessage -From 'database.alerts@testmail.com' -To %EMAIL_RECIPIENT% -Subject '%EMAIL_SUBJECT%' -Body '%EMAIL_BODY%' -SmtpServer '192.24.1.40' -Attachments '%ATTACHMENTS%'"

)

del E:\TABLESPACE_USAGE.txt

Open task scheduler and follow the following steps to schedule the batch script to run every 1 hour.