1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
| CREATE OR REPLACE PROCEDURE send_mail (v_mail_to IN VARCHAR2,
v_mail_from IN VARCHAR2,
v_mail_body IN VARCHAR2,
v_mail_subject IN VARCHAR2,
v_mail_smtp_host IN VARCHAR2,
v_mail_smtp_port IN NUMBER DEFAULT 25)
IS
v_mail_conn utl_smtp.connection;
v_mail_from VARCHAR2(255):='toto@ttt.com';
v_mail_to VARCHAR2(255):='toto@ttt.com';
v_mail_subject VARCHAR2(255):='Etat des tablespace';
v_mail_body VARCHAR2(4000) := 'select T1.tablespace_name as "Nom_tablespace",
T1.bytes / 1024 / 1024 as "Espace_utilisé (Mb)",
T2.bytes / 1024 / 1024 as "Espace_libre (Mb)",
T2.largest /1024 /1024 as "Largest (Mb)",
round(((T1.bytes-T2.bytes)/T1.bytes)*100,2) as "Pourcentage_utilisé",
case
when round(((T1.bytes-T2.bytes)/T1.bytes)*100,2) >= 83
then 1
else
0
end
flag_envoi_mail
from
(
select tablespace_name,
sum(bytes) as bytes
from dba_data_files
group by tablespace_name
)
T1,
(
select tablespace_name,
sum(bytes) as bytes ,
max(bytes) as largest
from dba_free_space
group by tablespace_name
)
T2
where T1.tablespace_name=T2.tablespace_name
order by ((T1.bytes-T2.bytes)/T1.bytes) desc ;';
BEGIN
utl_smtp.helo(v_mail_conn, 'domaine.fr');
utl_smtp.mail(v_mail_conn, v_mail_from);
utl_smtp.rcpt(v_mail_conn, v_mail_to);
utl_smtp.open_data(v_mail_conn);
utl_smtp.write_data(v_mail_conn, 'Subject: ' || v_mail_subject || utl_tcp.crlf);
utl_smtp.write_data(v_mail_conn, 'From: ' || v_mail_from || utl_tcp.crlf);
utl_smtp.write_data(v_mail_conn, 'To: ' || v_mail_to || utl_tcp.crlf);
utl_smtp.write_data(v_mail_conn, utl_tcp.crlf);
utl_smtp.write_data(v_mail_conn, v_mail_body || utl_tcp.crlf);
utl_smtp.close_data(v_mail_conn);
utl_smtp.quit(v_mail_conn);
EXCEPTION
WHEN utl_smtp.Transient_ErrorORutl_smtp.Permanent_Error then
raise_application_error(-20000,'Unabletosendmail',TRUE);
END;
/ |
