Batch script to check tablespace usage and send email notification

Ameer Khan

Apr 10, 2024

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.