监控数据库表空间并自动添加文件

数据仓库表空间增长比较快，为了减少处理时间及解决监控问题，使用存储过程自动处理并调用短信接口通知

--1、监控视图及表空间配置表
create or replace view dba_tablespace_free as
select a.tablespace_name,
       a.total_space_mb allocated_space_mb,
       round(b.free_space_mb, 2) free_space_mb,
       (a.max_space - a.total_space_mb) free_allocate_mb,
       round(a.max_space, 2) max_space_mb,
       round((a.total_space_mb - b.free_space_mb) / a.total_space_mb * 100,
             2) pct_usage,
       round(a.total_space_mb / a.max_space * 100, 2) pct_allocated
  from (select tablespace_name,
               sum(bytes) / 1024 / 1024 total_space_Mb,
               decode(sum(maxbytes / 1024 / 1024),
                      0,
                      sum(bytes) / 1024 / 1024,
                      sum(case
                            when AUTOEXTENSIBLE = 'YES' then
                             maxbytes
                            else
                             bytes
                          end) / 1024 / 1024) max_space
          from dba_data_files
         group by tablespace_name) a,
       (select tablespace_name, sum((bytes) / 1024 / 1024) free_space_Mb
          from dba_free_space
         group by tablespace_name) b
 where a.tablespace_name = b.tablespace_name;

 表空间配置表
 create table DBA_TABLESPACE_MONITOR
(
  tablespace_name VARCHAR2(30),
  threshold_g     NUMBER,
  add_size_g      NUMBER
)
--2、调短信接口存储过程
CREATE OR REPLACE PROCEDURE SEND_MESSAGE(msg varchar2,send_user varchar2) as
  /************************************************************************************
  *      Copyright (C) 2013 xxx,All Rights Reserved
  *      Name:
  *        SEND_MESSAGE
  *      Description:
  *        调用短信接口发短信
  *      Author:
  *        olivianing
  *      Finish DateTime:
  *        2013年4月8日
  *      Parameter: 消息内容
  ***********************************************************************************/
req UTL_HTTP.req;
resp UTL_HTTP.resp;
begin
  req  := UTL_HTTP.BEGIN_REQUEST('http://172.25.6.183/cgi-bin/tbi_send_msg1?msg='||msg||'#user='||send_user);
  resp := UTL_HTTP.GET_RESPONSE(req);
  utl_http.end_response(resp);
EXCEPTION
  WHEN utl_http.end_of_body THEN
    utl_http.end_response(resp);
end;

--3、自动处理
CREATE OR REPLACE PROCEDURE "P_MONIT_TBS" as
  /************************************************************************************
  *      Copyright (C) 2013 xxx,All Rights Reserved
  *      Name:
  *        P_MONIT_TBS
  *      Description:
  *        监控数据库Asm、表空间容量并自动添加表空间
  *      Author:
  *        olivianing
  *      Finish DateTime:
  *        2013年4月8日
  *      Parameter: null
  ***********************************************************************************/
  vMessage   varchar2(1000);
  v_name     varchar2(200);
  vDb_name   varchar2(100);
  cn         integer;
  tb_cn      integer;
  lv_errinfo varchar2(200);
  v_sql      varchar2(1000);
BEGIN
  --查看数据库名
  select name into vDb_name from v$database;
  --查看表空间是否空间不足
  select count(1)
    into tb_cn
    from dba_tablespace_monitor a, dba_tablespace_free b
   where a.tablespace_name = b.tablespace_name
     and b.free_space_mb < a.threshold_g;
  if tb_cn > 0 then
  --判断磁盘组是否大于300G
  select count(1) into cn from V$asm_Diskgroup where free_mb / 1024 > 300;
  if cn = 0 then
    vMessage := vDb_name || 'Asm_space_less_than_300G';
  else
    if cn >= 1 then
      vMessage := vDb_name || '_MONITOR';
      --根据配置表监控表空间
      for x in (select a.tablespace_name tablespace_name,
                       b.free_space_mb   free_space_mb,
                       a.add_size_g      add_size_mb
                  from dba_tablespace_monitor a, dba_tablespace_free b
                 where a.tablespace_name = b.tablespace_name
                     
                   and b.free_space_mb < a.threshold_g) loop
        begin
          vMessage := vMessage || '--tbs_' || x.tablespace_name ||
                      '_is_only_' || x.free_space_mb || 'MB';
          select name
            into v_name
            from (select *
                    from V$asm_Diskgroup
                   where free_mb / 1024 > 300
                   order by free_mb desc)
           where rownum = 1;
          v_sql := 'alter tablespace ' || x.tablespace_name ||
                   ' add datafile ''+' || v_name || ''' size ' ||
                   x.add_size_mb || 'm';
          --dbms_output.put_line(v_sql);
          begin
            execute immediate v_sql;
            vMessage := vMessage || '_add_datafile_success';
          end;
        EXCEPTION
          when others THEN
            lv_errinfo := SQLERRM;
            vMessage   := vMessage || '_add_datafile_failure';
         
        end;
      end loop;
    end if;
  end if;
  send_message(vMessage, 'olivianing');
  end if;
end;

--4、删除job

--
BEGIN
  DBMS_SCHEDULER.drop_job (job_name  => 'TBS_MONITOR_SCHEDULE');
END;
/

--5、创建job
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'TBS_MONITOR_SCHEDULE',
job_type => 'STORED_PROCEDURE',
job_action => 'SYS.P_MONIT_TBS',
start_date => sysdate,
enabled       => TRUE,
comments => '---this is tablespace monitor---',
repeat_interval => 'FREQ=HOURLY;INTERVAL=6');
END;
/

select * from dba_scheduler_jobs where job_name='TBS_MONITOR_SCHEDULE'

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

转载于:http://blog.itpub.net/28598517/viewspace-758355/