监控数据库表空间增长量

--创建基表EDBA_TABLESPACE_USAGE_METRICS
create table EDBA_TABLESPACE_USAGE_METRICS as 
SELECT TABLESPACE_NAME ,
ROUND(TABLESPACE_SIZE * 8 / 1024, 2) AS total_m,
ROUND(USED_SPACE * 8 / 1024 , 2) AS used_m,
ROUND(TABLESPACE_SIZE * 8 /  1024, 2) -
ROUND(USED_SPACE * 8 / 1024, 2) AS free_m,
sysdate AS sample_time
FROM DBA_TABLESPACE_USAGE_METRICS
ORDER BY USED_PERCENT desc;

再用存储过程插入每天的表空间使用情况

CREATE OR REPLACE PROCEDURE shot_tablespace_usage_metrics Authid CURRENT_USER IS
  --/************************************************************************************
  -- 程序名称:    shot_tablespace_usage_metrics
  -- 功能描述:    采集表空间使用情况
  -- 输入资源:
  -- 输出资源:
  -- 中间资源:    <用户名>.<中间表或视图等对象名>
  -- 创建人员:    HOUZHIQING
  -- 创建日期:    20170206
  -- 版本说明:    V1.0
  -- 公司名称:    shsnc
  --/************************************************************************************

  v_sql VARCHAR2(10000) DEFAULT ''; -- 动态SQL变量,注意SQL长度
BEGIN

  --插入当前表空间使用情况
  v_sql := 'insert into  EDBA_TABLESPACE_USAGE_METRICS  
SELECT TABLESPACE_NAME ,
ROUND(TABLESPACE_SIZE * 8 / 1024, 2) AS total_m,
ROUND(USED_SPACE * 8 / 1024 , 2) AS used_m,
ROUND(TABLESPACE_SIZE * 8 /  1024, 2) -
ROUND(USED_SPACE * 8 / 1024, 2) AS free_m,
sysdate AS sample_time
FROM DBA_TABLESPACE_USAGE_METRICS
ORDER BY USED_PERCENT desc';
  EXECUTE IMMEDIATE v_sql;
  COMMIT;

  --删除2个月之前的数据
  v_sql := 'delete from EDBA_TABLESPACE_USAGE_METRICS where to_char(sample_time,''
           YYYYMMDD '')<to_char(add_months(sysdate,-2),'' YYYYMMDD'')';
  EXECUTE IMMEDIATE v_sql;
  COMMIT;
END;

 

创建一个JOB每天定时采集数据

--每天16:00定时运行采集表空间的JOB
declare
  job number;
begin
  dbms_job.submit(job, 'shot_tablespace_usage_metrics;', sysdate, 'TRUNC(SYSDATE+1)+(16*60)/(24*60) ');
end;

查询日增长的方式

--查询日增长量
select now.tablespace_name,
       now.total_m,
       now.used_m,
       now.free_m,
       now.used_m - befor.used_m increa_m,
       case
         when befor.sample_time is null then
          'NEW TABLESPACE'
         when befor.sample_time is not null then
          befor.sample_time || '_to_' || now.sample_time
       end date_cycle
  from (select tablespace_name,
               total_m,
               used_m,
               free_m,
               to_char(sample_time, 'YYYYMMDDHH24MISS') sample_time
          from EDBA_TABLESPACE_USAGE_METRICS
         where to_char(sample_time, 'YYYYMMDD') =
               to_char(sysdate, 'YYYYMMDD')) now,
       (select tablespace_name,
               total_m,
               used_m,
               free_m,
               to_char(sample_time, 'YYYYMMDDHH24MISS') sample_time
          from EDBA_TABLESPACE_USAGE_METRICS
         where to_char(sample_time, 'YYYYMMDD') =
               to_char(sysdate - 1, 'YYYYMMDD')) befor
 where now.tablespace_name = befor.tablespace_name(+)
 order by increa_m desc;