Oracle 邮件定时发送表空间数据文件信息

Oracle数据文件监控与邮件通知

最新推荐文章于 2025-09-05 14:24:44 发布

weixin_33895604

最新推荐文章于 2025-09-05 14:24:44 发布

阅读量154

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：https://juejin.im/post/5a3136fb6fb9a045132ab328

本文介绍如何在Oracle数据库中查询数据文件信息，并通过PL/SQL程序将这些信息整理成CSV格式，最后利用UTL_MAIL包实现定时邮件通知功能。

##查询数据文件信息

-- 查看数据文件信息
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
复制代码