【自动化管理】自动维护管理表空间

create or replace procedure p_monit_tbs_1  as
  --created by huangchao,2011-09-13
  --监控表空间增量
  vMessage         varchar(250); --下发短信
  vDb_name         varchar2(10); --数据库名
  vTBS_FREE_GB     number; --剩余表空间阀值
  monitor_date     DATE := trunc(sysdate-2);
  v_name           varchar2(200);
  cn integer;
  lv_errinfo  varchar2(200);
  v_sql      varchar2(1000);
BEGIN
  --制定监控的目标
  vTBS_FREE_GB := 35;
   --获取数据库名
  select name into vDb_name from v$database;
vDb_name:='NEW_'||vDb_name;
  --打开游标获取表空间的剩余数
  for x in (SELECT tablespace_name,(free_space_mb+free_allocate_mb)total_free_mb FROM dba_tablespace_free
 WHERE tablespace_name NOT IN ('OSS01_HIS_DATA','TEST1_DATA','GG_DATA','TEST_TBS1') ) loop
  --对监控的值做判断
    if (round(x.total_free_mb/1024,2)<= vTBS_FREE_GB) then
      begin
        vMessage := '[报警邮件] 数据库' || vDb_name ||
                    '表空间'|| x.tablespace_name||'还剩'||x.total_free_mb|| 'MB';
        --对DBA进行发送邮件监控
        --判断磁盘组是否大于200G
        select count(1) into cn  from V$asm_Diskgroup where free_mb/1024 >200;
        if cn =0 then
          sendmail(Subject =>'新库每个磁盘组文件不足200G' ,
          v_Msg => '新库每个磁盘组文件不足200G',Receipint => 'lakersman@qq.com');
            else if cn>=1 then
          select name into v_name from
          (select * from V$asm_Diskgroup where free_mb/1024 >200 order by free_mb desc) where rownum =1;
         v_sql := 'alter tablespace '||x.tablespace_name||' add datafile ''+'||v_name||''' size 256M autoextend on next 100M';
          begin
          execute immediate v_sql;
           for x in
             (select mail from Moni_Alert_Mail where flag = 1)
             ---从邮件配置表里面读取邮件配置信息
              loop
         sendmail(Subject =>'表空间不足,自动添加数据文件成功' ,v_Msg => vmessage||chr(10)||v_sql,Receipint => x.mail);
           end loop;
          EXCEPTION
           when others THEN
     lv_errinfo := SQLERRM;
    sendmail(Subject =>'自动添加表空间数据文件报错' ,v_Msg => v_sql||lv_errinfo,Receipint => 'lakersman@qq.com');
       end;
         end if;
     end if;
      end;
    end if;
  end loop;
  COMMIT;
end;