Script for tablespace utilization alert with UTL MAIL package

Configured mail alert with utl mail package

UTL_MAIL is the oracle internal utility for sending mail through the database.

First configure the UTL_MAIL utility:

1. Need to configure the SMTP address in Oracle Environment.

alter system set smtp_out_server='mail.server.com' scope=both;

2. Need to create UTL_MAIL package if not exists in oracle Environment with following script:

SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql;
SQL> @$ORACLE_HOME/rdbms/admin/prvtmail.sql;

3. If you want to execute from specific user you need to give execute permission on it.

grant execute utl_mail to user;

Script to send mail for table-space utilization above 70%:
Script will run the SQL query which will fetch tablespace utilization above 70% and it will send an html mail with utl_mail utility:
Note: Configure and put the mail id in utl_mail package.

Script:

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 data above 70% utilization
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;

BEGIN

--Created and Modified by sandeep on 5-Nov-2018

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>
<td colspan="6" >Report for Tablespace Utilization dated: '||var_date||'</td>
</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 tablspace output having more than 70%
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||'

</tbody>
</table>
<br></br>
';

--For testing output of VAR variable it is pure HTML output.
---dbms_output.put_line(var);

-- Sending Mail Utility in HTML format.

if var_Result > 0 then
UTL_MAIL.send(sender => 'sender@email',recipients => 'recipient@email',subject => 'Alert for tablespace utilized above 70%',message => var,mime_type => 'text/html;charset=us-ascii');
end if;

end;
/

Output

Tablespace monitoring

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.