Script for monitoring windows drive with oracle tablespace space
Created the script for monitoring the tablespace and windows drive with UTL_MAIL functionality.
Script is divided into two parts:
1. Using windows command (bat file) to get info into CSV file of windows drive information.
2. Use procedure for fetching tablespace information and use external function for using CSV data as a table and then convert both information into html format and send the Mail to 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.
--create directory as location of CSV file.
Creete directory dbscript as 'D:\scripts';
--I am using sys user so need not to give permission.
GRANT READ, WRITE ON DIRECTORY MY_DIR TO USER;
5. Create external table in the Oracle
----drop table ext_diskinfo;
CREATE TABLE ext_diskinfo
(DISK_ID VARCHAR2(30),
TOTAL_SPACE VARCHAR2(30),
FREE_SPACE VARCHAR2(30)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY dbscript
ACCESS PARAMETERS
(
records delimited by newline
badfile dbscript:'ext_diskinfo%a_%p.bad'
logfile dbscript:'ext_diskinfo%a_%p.log'
fields terminated by ','
missing field values are null
( DISK_ID , TOTAL_SPACE, FREE_SPACE
)
)
LOCATION ('diskinfo.csv')
)
PARALLEL
REJECT LIMIT UNLIMITED;
6. Test the External table is giving output of Drive information
select DISK_ID,TOTAL_SPACE/1024/1024/1024,FREE_SPACE/1024/1024/1024 from ext_diskinfo where TOTAL_SPACE is not null;
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
exec Proc_emailalert_tablespace_70
9. If you getting error then please check SMTP parameter and utl mail is configured on DB Server.
Smart way of Technology 
amazing script.
is there a way to send an email report only if a tablespace percentage is above 80%?
LikeLike