--Call SEND_MAIL_X or SEND_MAIL
DECLARE
tablespace_name VARCHAR2(50);
total_space VARCHAR2(50);
used_space VARCHAR2(50);
pct_free VARCHAR2(50);
P_TXT VARCHAR2(20000) := '';
P_SUB VARCHAR2(50) := 'subject';
P_SENDOR VARCHAR2(50) := 'finch.chen@linde-lienhwa.com.tw';
P_RECEIVER VARCHAR2(50) := 'finch.chen@linde-lienhwa.com.tw';
P_SERVER VARCHAR2(50) := '172.30.160.3';
P_PORT NUMBER := 25;
P_NEED_SMTP INT := 0;
P_USER VARCHAR2(50) DEFAULT NULL;
P_PASS VARCHAR2(50) DEFAULT NULL;
P_FILENAME VARCHAR2(50) DEFAULT NULL;
P_ENCODE VARCHAR2(50) DEFAULT 'bit 7';
CURSOR table_status IS
select a.tablespace_name, a.total || 'm' total_space, (a.total - b.free) || 'm' used_space,
to_char((a.total - b.free) / a.total * 100, '99.99') || '%' pct_free
from
(select tablespace_name, sum(bytes) / 1024 / 1024 total from dba_data_files group by tablespace_name) a,
(select tablespace_name, sum(bytes) / 1024 / 1024 free from dba_free_space group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by pct_free desc;
BEGIN
P_TXT := '
<html>
<head>
</head>
<body>
<Table border="1">
<tr bgcolor="#d3d3d3"><td>Tablespace Name</td><td> Total Space</td><td>Used Space</td><td>Used%</td></tr>
';
FOR a IN table_status LOOP
P_TXT := P_TXT||'<tr><td>'||a.tablespace_name||'</td><td>'|| a.total_space||'</td><td>'|| a.used_space||'</td><td>'|| a.pct_free||'</td></tr> ';
END LOOP;
P_TXT := P_TXT||'</Table></body></html> ';
SEND_MAIL_X(
P_TXT ,
P_SUB ,
P_SENDOR ,
P_RECEIVER ,
P_SERVER ,
P_PORT ,
P_NEED_SMTP ,
P_USER ,
P_PASS ,
P_FILENAME ,
P_ENCODE );
END;
No comments:
Post a Comment