数据仓库表空间增长比较快,为了减少处理时间及解决监控问题,使用存储过程自动处理并调用短信接口通知
--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(''||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'