Batch script to test tablespace utilization and ship e mail notification

Subject: We had a requirement for a buyer, the place oracle 19c
database was working on Home windows server 2019. Buyer needed to observe
tablespace’s datafile utilization and get e mail alert incase the used house is
lower than 80%

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

Step 1.

First we create the sql which will likely be used to observe the
tablespace utilization. Lets reserve it as E:BackupScriptsTbs_size_check.sql

Set strains 222

SELECT
d.tablespace_name “TS Identify”, d.num_files “Num Information”,

d.asize “Dimension MB”, NVL
(f.freebytes, 0) “Free MB”,

(d.BYTES – NVL (f.freebytes, 0))
“Used MB”,

((d.BYTES – NVL (f.freebytes, 0)) *
100) / d.asize “P.c 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(biggest(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 likely be used to
monitor the tablespace utilization and ship an alert if the utilization hits a predefined threshold
worth. In our case now we have set threshold worth to 80%. This may be modified as
per the requirement.

@echo on

REM Database credentials

set DB_USER=sys

set DB_PASS=passfordb

set DB_SID=orcl

REM Electronic mail configuration

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

set EMAIL_SUBJECT=Tablespace Utilization Alert

set FROM_EMAIL=database.alerts@testmail.com

set HOSTNAME=%COMPUTERNAME%

set EMAIL_BODY=Tablespace utilization is LOW at %HOSTNAME%. Please take acceptable motion.

set ATTACHMENTS=E:TABLESPACE_USAGE.txt

REM Execute SQL queries utilizing SQL*Plus

REM Execute SQL question and ship e mail if threshold is met

E:apporacleproduct19.3.0dbhome_1binsqlplus -S %DB_USER%/%DB_PASS%@%DB_SID% as sysdba @E:BackupScriptsTS_SPACE_CHECK1.SQL > E:TABLESPACE_USAGE.txt

cd E:

REM Learn utilization % and tablespace title 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 Verify if usage_percent is bigger than or equal to the brink

if %usage_percent% GEQ 80 (

REM Ship e mail alert with tablespace title and utilization proportion

powershell -ExecutionPolicy Bypass -Command “Ship-MailMessage -From ‘database.alerts@testmail.com’ -To %EMAIL_RECIPIENT% -Topic ‘%EMAIL_SUBJECT%’ -Physique ‘%EMAIL_BODY%’ -SmtpServer ‘192.24.1.40’ -Attachments ‘%ATTACHMENTS%'”

)

del E:TABLESPACE_USAGE.txt

Open job scheduler and observe the next steps to
schedule the batch script to run each 1 hour.