表空间邮件预警(luckyfriends)

表空间邮件预警
文章版权所有Jusin Hao（luckyfriends），支持原创，转载请注明。
----某些数据库可以单独创建用户级对应的表空间
create tablespace yw_cux_data  datafile '/u02/lsdb/yw_cux_data01.dbf' size 100m;
create user lsdb_yw identified by ****** default tablespace yw_cux_data temporary  tablespace temp;
grant connect,dba to lsdb_yw;
grant select on DBA_FREE_SPACE to lsdb_yw;
grant select on  DBA_DATA_FILES to lsdb_yw;
grant execute on sys.UTL_TCP to lsdb_yw;
grant execute on sys.UTL_SMTP to lsdb_yw;
grant execute on SYS.UTL_INADDR to lsdb_yw;
grant execute on utl_mail to lsdb_yw
---创建存放邮件的表并插入接收人的邮件地址
conn lsdb_yw/*******
CREATE TABLE TAB_SEND_MAIL
(
  NUM_ID        NUMBER,
  EMAIL_ADDRESS  VARCHAR2(50)
);
insert into TAB_SEND_MAIL values (1,'TEST@163.com');
commit;


SQL> select comp_name from dba_registry;
 
COMP_NAME
--------------------------------------------------------------------------------
Oracle Database Catalog Views
Oracle Database Packages and Types
Oracle Workspace Manager
JServer JAVA Virtual Machine
Oracle XDK
Oracle Database Java Packages
Oracle Expression Filter
Oracle Data Mining
Oracle Text
Oracle XML Database
Oracle Rules Manager
Oracle interMedia
OLAP Analytic Workspace
Oracle OLAP API
OLAP Catalog
Spatial
Oracle Enterprise Manager
 
17 rows selected
SQL> select global_name,utl_inaddr.get_host_address from global_name;
 
GLOBAL_NAME                                                                      GET_HOST_ADDRESS
-------------------------------------------------------------------------------- --
LSDB                                                                             10.12.22.21


----如果没有安装XML database则执行如下脚本：
SQL>  @$ORACLE_HOME/rdbms/admin/utlsmtp.sql
SQL> @$ORACLE_HOME/rdbms/admin/utltcp.sql
SQL> @$ORACLE_HOME/rdbms/admin/catqm.sql xdb sysaux temp YES
 
SQL> select comp_name , status from dba_registry;
 COMP_NAME                                                                        STATUS
-------------------------------------------------------------------------------- --
Oracle XML Database                                                              VALID
Oracle Database Catalog Views                                                    VALID
Oracle Database Packages and Types                                               VALID
---授权查询权限
grant select on DBA_FREE_SPACE to haohao;
grant select on  DBA_DATA_FILES to haohao;


----可能会碰到没有访问ACL的权限，则需执行如下： 
---创建一个ACL
BEGIN
   DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
        acl          =>'cux_application_acl.xml',
        description  => 'ACL for users of my application.',
        principal    => 'HAOHAO',
        is_grant     => TRUE,
        privilege    => 'resolve',
        start_date   => null,
        end_date     => null
  );
commit;
END;
/
---给用户增加访问网络的权限
begin
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl       => 'cux_application_acl.xml',
                                       principal => 'HAOHAO',
                                       is_grant  => true,
                                       privilege => 'resolve');
commit;
end;
/
 
---给用户增加访问网络的权限connect
begin
    DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl       => 'cux_application_acl.xml',
                                         principal => 'HAOHAO',
                                       is_grant  => true,
                                       privilege => 'connect');
commit;
end;
/
 
---指派ACL
BEGIN
DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'cux_application_acl.xml',
    host        => '10.12.22.23',
    lower_port  => 25,
    upper_port  => NULL);
  COMMIT;
END;
/
 
 
SQL> select any_path from resource_view where any_path like '/sys/acls/%.xml';
 ANY_PATH
--------------------------------------------------------------------------------
/sys/acls/all_all_acl.xml
/sys/acls/all_owner_acl.xml
/sys/acls/bootstrap_acl.xml
/sys/acls/cux_application_acl.xml
/sys/acls/ro_all_acl.xml
 
SQL> SELECT host, lower_port, upper_port, acl FROM dba_network_acls;
 HOST               LOWER_PORT UPPER_PORT ACL
-------------------------------------------------------------------------------- --
10.12.22.23        25         25 /sys/acls/cux_application_acl.xml
 
ACL                                                     PRINCIPAL    PRIVILEGE             IS_GRANT        START_DATE     END_DATE
-------------------------------------------------------------------------------- --
/sys/acls/cux_application_acl.xml  SYSADM      connect               true                          
/sys/acls/cux_application_acl.xml  SYSADM      resolve               true                          
 




----创建发送邮件存储过程
CREATE OR REPLACE PROCEDURE CUX_SEND_MAIL(SUBJECT IN VARCHAR2,
              CONTENTSED IN VARCHAR2
) IS
  EMAIL_SERVER VARCHAR2(30) := '127.0.0.1';
  SENDER_ADDRESS  VARCHAR2(50) := 'root@test.com';--发件地址
  RECEIVER_ADDRESS  VARCHAR2(30);                 --收件人地址变量
  PORT NUMBER := 25;
  CONN UTL_SMTP.CONNECTION;
  MESG VARCHAR2(4000);
  cursor c_mail is select email_address from TAB_SEND_MAIL;   --收件人地址表


BEGIN
-----------------------
for c_mail_v in c_mail
  loop
    RECEIVER_ADDRESS:=c_mail_v.email_address;
    CONN:= UTL_SMTP.OPEN_CONNECTION(EMAIL_SERVER,PORT);
    UTL_SMTP.HELO(CONN,EMAIL_SERVER);
    UTL_SMTP.MAIL(CONN,SENDER_ADDRESS);
    UTL_SMTP.RCPT(CONN,RECEIVER_ADDRESS);
    MESG:= 'CONTENT-TYPE: TEXT/PLAIN; CHARSET=zhs16gbk' || UTL_TCP.CRLF ||
    'DATE:' || TO_CHAR(SYSDATE,'YYYYMMDD HH24:MI:SS') || UTL_TCP.CRLF ||
    'FROM:' || SENDER_ADDRESS || UTL_TCP.CRLF ||
    'SUBJECT:' || SUBJECT || UTL_TCP.CRLF ||
    'TO: '|| RECEIVER_ADDRESS || UTL_TCP.CRLF ||
    'CONTENT-TYPE: TEXT/PLAIN; CHARSET=zhs16gbk' || UTL_TCP.CRLF ||
    '' || UTL_TCP.CRLF || CONTENTSED || UTL_TCP.CRLF ;
    UTL_SMTP.OPEN_DATA(CONN);
    UTL_SMTP.WRITE_RAW_DATA(CONN,UTL_RAW.CAST_TO_RAW(MESG));
    UTL_SMTP.CLOSE_DATA(CONN);
    UTL_SMTP.QUIT(CONN);
  end loop;
END;


----创建表空间使用率预警存储过程
CREATE OR REPLACE PROCEDURE CUX_TBS_ALERT(v_percent in number default 85)
as
  SUBJECT    varchar2(100);
  CONTENT    varchar2(4000);
  CONTENT1  varchar2(4000);
  p_v2   number;
  p_v1   number;
  p_percent number;




BEGIN
    p_percent:=v_percent;


------根据输入判断百分比
  select count(*) into p_v2 from (select tablespace_name,sum(a.bytes) db from DBA_DATA_FILES a group by tablespace_name) d,
  (select tablespace_name ,sum(b.bytes) fb from DBA_FREE_SPACE b group by tablespace_name) f where
  d.tablespace_name=f.tablespace_name and
  ((d.db-f.fb)/d.db)*100>p_percent;
if p_v2>0 then
    SUBJECT:='PROD-DB Tablespace Used Space Alert Info';
  CONTENT1:='表空间超过预警值'||p_percent||'%'||chr(13)||chr(10)||'表空间名   占用百分比';
  for it1 in (
   SELECT UPPER(F.TABLESPACE_NAME) name,
         --D.TOT_GROOTTE_MB sized,
         --D.TOT_GROOTTE_MB - F.TOTAL_BYTES used,
         TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100, 2), '99.99') pec
       --F.TOTAL_BYTES ,
       --F.MAX_BYTES
     FROM (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
               ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
          FROM SYS.DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) F,
       (SELECT DD.TABLESPACE_NAME,
               ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
          FROM SYS.DBA_DATA_FILES DD
         GROUP BY DD.TABLESPACE_NAME) D
    WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
    and (D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100>p_percent
    order by pec desc)


  loop


    CONTENT:=it1.name||it1.pec;
  CONTENT1:=CONTENT1||chr(13)||chr(10)||CONTENT;


  end loop;
    cux_send_mail(SUBJECT,CONTENT1); ---发送邮件过程
end if;
end;


----创建调用预警存储过程的job，第一次执行为第二天8:30，然后每隔两小时执行一次
DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
    ( job       => X 
     ,what      => 'begin CUX_TBS_ALERT(90); end;'
     ,next_date => to_date(to_char(sysdate+1,'yyyy-mm-dd')||' 08:30:00','yyyy-mm-dd hh24:mi:ss')            
     ,interval  => 'SYSDATE+2/24'  
     ,no_parse  => TRUE
    );
commit;
END;
/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14710393/viewspace-1074849/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14710393/viewspace-1074849/