Oracle 邮件定时发送表空间数据文件信息
2017-02-22 本文已影响0人
Kahn
查询数据文件信息
-- 查看数据文件信息
select
b.file_name 物理文件名,
b.tablespace_name 表空间,
b.bytes/1024/1024 大小M,
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 已使用M,
substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) 利用率
from dba_free_space a
right join dba_data_files b
on a.file_id=b.file_id
group by b.tablespace_name,b.file_name,b.bytes
order by b.tablespace_name
配置邮件发送
- 设置
参考 Oracle 10G中轻松发送email -- UTL_MAIL - 测试邮件发送
begin
utl_mail.send(sender=>'oracle@example.com',
recipients=>'dba@example.com',
subject=>'this is mail subject',
message=>'this is mailmessage');
end;
/
pl/sql程序
declare
boyer_date number := 0;
lv_subject VARCHAR2(200) := 'Oracle datafiles info for ';
lv_sender VARCHAR2(200) := 'oracle@example.com';
lv_recipients VARCHAR2(200) := 'dba@example.com';
lv_priority PLS_INTEGER := NULL; -- Configurable
lv_last BOOLEAN := FALSE;
lv_count NUMBER := 0;
lv_message VARCHAR2(32000):= 'FileName, TablespaceName, Size, Used, % Used'||chr(13);
CURSOR cur_query
IS
/* Insert your query here */
select
b.file_name a,
b.tablespace_name b,
b.bytes/1024/1024 c,
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 d,
substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) e
from dba_free_space a
right join dba_data_files b
on a.file_id=b.file_id
group by b.tablespace_name,b.file_name,b.bytes
order by b.tablespace_name;
BEGIN
/* The following will get today's date */
select TO_CHAR(CURRENT_DATE, 'YYYYMMDD') into boyer_date FROM dual;
lv_subject := lv_subject || boyer_date;
FOR rec IN cur_query
LOOP
/* Depending on the number of columns being returned in the query, the lv_message could be quite large */
lv_message := lv_message || rec.a||','||rec.b||','||rec.c||','||rec.d||','||rec.e||chr(13);
END LOOP;
UTL_MAIL.send_attach_varchar2(
sender => lv_sender,
recipients => lv_recipients,
subject => lv_subject,
message => 'Here is the spreadsheet for ' || boyer_date,
attachment => lv_message,
att_filename => 'data_' || boyer_date || '.csv'
);
END;
/
SHOW ERRORS
定时发送
todo
