Configured tablespace utilization mail alert from UTL MAIL package

Configured mail alert with utl mail package

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

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;

  1. 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;

 
If you want to execute from specific  user you need to give execute permission on it.
grant execute utl_mail to ;
 
Script to send mail for tablespace 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: put the mailid in utl_mail package.

Code:

declare
var_name varchar2(35);
var_hostname varchar2(25);
var_mode varchar2(10);
var_result number;
var varchar2(1000);
cursor cn is
select a.tablespace_name,
a.bytes_alloc/(1024*1024) "TOTAL_ALLOC_MB",
a.physical_bytes/(1024*1024) "TOTAL_PHYS_ALLOC_MB",
nvl(b.tot_used,0)/(1024*1024) "USED_MB",
(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

var_result := 0;

var := '<HTML><table border=1><tr><th>Tablespace Name</th><th>Alloc Space(MB)</th><th>Physical Alloc(MB)</th><th>Used(MB)</th><th>Percent Used</th></tr>';

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_MB,99999.99)||'</TD><td>'||to_char(data_cur.TOTAL_PHYS_ALLOC_MB,99999.99)||'</td><td>'||to_char(data_cur.USED_MB,99999.99)||'</td><td>'||to_char(data_cur.perc_used,999.99)||'</td></tr>';
--var := var || '<tr><td>k</td><TD></tr>';
--dbms_output.put_line(var);
end loop;

select name,open_mode into var_name,var_mode from v$database;
select host_name into var_hostname from v$instance;

var := var||'<tr><td colspan=5>DB Name:'||var_name||' Hostname:'||var_hostname||'</td></tr></table></html>';
dbms_output.put_line(var);

if var_Result > 0 then

UTL_MAIL.send(sender => 'Email id',
recipients => 'Email id',
subject => 'Alert for Tablespace Utilized above 70%',
message => var,
mime_type => 'text/html;charset=us-ascii');

end if;
end;
/

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s