linux创建文件自动添加数据,监控数据库表空间并自动添加文件

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

--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'