Monitor Oracle Tablespace and Windows Drive Using Script

Created the script for monitoring the tablespace and Windows drive with UTL_MAIL functionality.
The script is divided into two parts:
1. Using a Windows command (bat file) to get info into a CSV file of Windows drive information.
2. A procedure is used for fetching tablespace information, and an external function is used for treating CSV data as a table, then converting both pieces of information into HTML format and sending the mail to the user.

Output:

Steps to Configure

1. Create the RUNDISKINFO.bat script which scheduled in Task Scheduler.
Note: I am using D:\scripts location of the script change according to your need

Save it as rundiskinfo.bat
D:\scripts\diskinfo.bat localhost > D:\scripts\diskinfo.csv

2. Create the diskinfo.bat file.

@ECHO OFF

REM IF "%~1"=="" goto help

@SETLOCAL ENABLEEXTENSIONS
@SETLOCAL ENABLEDELAYEDEXPANSION

@FOR /F "skip=1 tokens=1" %%x IN ('"WMIC /node:"%1" LOGICALDISK GET Name " ') DO (
REM @ECHO %%x

@FOR /F "tokens=1-3" %%n IN ('"WMIC /node:"%1" LOGICALDISK GET Name,Size,FreeSpace | find /i "%%x""') DO ( @SET FreeBytes=%%n & @SET TotalBytes=%%p

SET TotalGB=0
SET FreeGB=0

REM Parameter value used to convert in GB
set num1=1074

@echo %%x,!TotalBytes!,!FreeBytes!
)
)
goto end
:error
echo.
echo *** Invalid server or drive specified ***
echo.
goto help

:help
echo.
echo diskfree.cmd
echo.
echo Queries remote server for free disk space.
echo Specify a MACHINENAME and a drive letter to be queried
echo.
echo Example: diskfree.cmd MACHINENAME c:
echo.
goto end
:end

3. Run the RUNDISKINFO.BAT will generate a file of D:\scripts\diskinfo.csv as output.

4. Now create an directory in database which is used by external table to read this diskinfo.csv file.

5. Create external table in the Oracle

6. Test the External table is giving output of Drive information.

7. Create procedure for fetching tablespace & disk info. Send them through mail.

-- exec Proc_emailalert_tablespace_70
set serveroutput on

create or replace procedure Proc_emailalert_tablespace_70
as
var_name VARCHAR2(35);
var_hostname VARCHAR2(25);
var_mode VARCHAR2(10);
var_result NUMBER;
var VARCHAR2(5000);
var_Date varchar2(15);

-- Cursor defining query which fetch all data
CURSOR cn IS
SELECT a.tablespace_name,
a.bytes_alloc/(1024*1024*1024) "TOTAL_ALLOC_GB",
a.physical_bytes/(1024*1024*1024) "TOTAL_PHYS_ALLOC_GB",
nvl(b.tot_used,0)/(1024*1024*1024) "USED_GB",
(a.bytes_alloc-b.tot_used)/(1024*1024*1024) "FREE_GB",
(nvl(b.tot_used,0)/a.bytes_alloc)*100 "PERC_USED"
FROM
(SELECT tablespace_name,
sum(bytes) physical_bytes,
sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
FROM dba_data_files
GROUP BY tablespace_name ) a,
(SELECT
tablespace_name,
sum(bytes) tot_used
FROM dba_segments
GROUP BY tablespace_name ) b
WHERE a.tablespace_name = b.tablespace_name (+)
and a.tablespace_name not in
(SELECT distinct tablespace_name FROM dba_temp_files)
--and a.tablespace_name not like 'UNDO%'
--and ((nvl(b.tot_used,0)/a.bytes_alloc)*100) > 70
ORDER BY 1;

--Cursor for fetching data from CSV file external table
Cursor diskinfo is
select DISK_ID,TOTAL_SPACE/1024/1024/1024 "TOTAL_GB",FREE_SPACE/1024/1024/1024 "FREE_GB",(TOTAL_SPACE-FREE_SPACE)/1024/1024/1024 "USED_GB",
(nvl((TOTAL_SPACE-FREE_SPACE),0)/TOTAL_SPACE)*100 "PERC_USED" from ext_diskinfo
where TOTAL_SPACE is not null;

-- Begin of coding
BEGIN

var_result := 0;
var := '';

-- Get database Name and Status
SELECT name,open_mode into var_name,var_mode FROM v$database;

--Get host name of machine
SELECT host_name into var_hostname FROM v$instance;

--Get today date
SELECT to_char(sysdate,'DD-MON-YYYY') into var_Date FROM dual;

--Define header of the table in html format
var := var||'<table border="1"><tbody>
<tr>
<th colspan="6" >Report for Tablespace Utilization dated: '||var_date||'</t>
</tr>
<tr>
<td colspan="6">Hostname: '||var_hostname||'</td>
</tr>
<tr>
<td colspan="6">DB Name: '||var_name||'</td>
</tr>
<tr>
<th>Tablespace Name</th>
<th>Alloc Space(GB)</th>
<th>Physical Alloc(GB)</th>
<th>Used(GB)</th>
<th>Free(GB)</th>
<th>Percent Used</th>
</tr>';

-- Loop for calculating multiple tablespace output
for data_cur in cn
loop
var_result := 1;

var := var ||'<tr>
<td>'||data_cur.tablespace_name||'</td>
<td>'||to_char(DATA_CUR.TOTAL_ALLOC_GB,9999999.99)||'</td>
<td>'||to_char(data_cur.TOTAL_PHYS_ALLOC_GB,9999999.99)||'</td>
<td>'||to_char(data_cur.USED_GB,9999999.99)||'</td>
<td>'||to_char(data_cur.FREE_GB,9999999.99)||'</td>
<td>'||to_char(data_cur.perc_used,999.99)||'</td>
</tr>';
end loop;

var := var || '<tr><th colspan="6">Disk information</th></tr>';

var := var||'<tr>
<th colspan=2>Disk Drive</th>
<th>Total(GB)</th>
<th>Used(GB)</th>
<th>Free(GB)</th>
<th>Percent Used</th>
</tr>';

--- procedure for output the diskspace ----
for data_cur1 in diskinfo
loop
var_result := 1;

var := var ||'<tr>
<td colspan=2>'||data_cur1.disk_id||'</td>
<td>'||to_char(DATA_CUR1.Total_GB,9999999.99)||'</td>
<td>'||to_char(data_cur1.USED_GB,9999999.99)||'</td>
<td>'||to_char(data_cur1.FREE_GB,9999999.99)||'</td>
<td>'||to_char(data_cur1.perc_used,999.99)||'</td>
</tr>';
end loop;

var := var||'</tbody></table><br></br>';

-- Sending Mail Utility in HTML format.

if var_Result > 0 then
UTL_MAIL.send(sender => 'sqlalerts@gmail.com',
recipients => 'sqlalerts@gmail.com',subject => 'Tablespace utilized info',message => var,mime_type => 'text/html;charset=us-ascii' );

end if;

end;
/

8. Execute the procedure

9. If you getting error then please check SMTP parameter and UTL mail is configured on DB Server.