记录数据库中，段大小的数据增长情况

 

 一、需求概述

客户数据库，空间不够，需要实时跟进，观察哪些对象的大小在增长。

oracle存在表空间的增长视图，但是对于segments,关注度不高。

本次使用自己创建定时任务，每天从dba_segments视图中获取信息，插入到日志表中，通过对日志表的sql查询运算，得到最终结果。

 二、测试环境实施

1）日志表
create table seg_tab_log
 (id number(20),
  owner varchar2(2000),
  segment_name varchar2(2000),
  segment_type varchar2(2000),
  tablespace_name varchar2(2000),
  Mbytes number(20,2));
  
  --测试SQL
insert into seg_tab_log
 select * from (
        select to_char(sysdate,'yyyymmdd') "date",owner,segment_name,segment_type,tablespace_name,round(sum(bytes)/1024/1024,2) Mbytes
        from dba_segments  group by owner,segment_name,segment_type,tablespace_name order by 5,4,3) a where a.Mbytes>50;   
OWNER      SEGMENT_NAME                   SEGMENT_TYPE                         TABLESPACE     MBYTES
---------- ------------------------------ ------------------------------------ ---------- ----------
SYS        RANGE_PART_TAB                 TABLE PARTITION                      SYSTEM            144
SYS        IDL_UB1$                       TABLE                                SYSTEM            272
SYS        SOURCE$                        TABLE                                SYSTEM             72
SYS        TEST_OBJ2                      TABLE                                SYSTEM            472
SYS        TEST_OBJ3                      TABLE                                SYSTEM            112
XDB        SYS_LOB0000069708C00025$$      LOBSEGMENT                           SYSAUX          54.19
SYS        INDEX_A_OWNER                  INDEX                                SYSTEM             80
SYS        TEST01                         TABLE SUBPARTITION                   SYSTEM            224
8 rows selected.

测试 plsql
SQL> set serveroutput on
declare
insert_sql varchar2(4000);
begin
insert into seg_tab_log
 select * from (
        select to_char(sysdate,'yyyymmdd') "date",owner,segment_name,segment_type,tablespace_name,round(sum(bytes)/1024/1024,2) Mbytes
        from dba_segments  group by owner,segment_name,segment_type,tablespace_name order by 5,4,3) a where a.Mbytes>50;
 commit;
end;
/

2）存储过程
create or replace procedure insert_seg_sql
 as begin
 insert into seg_tab_log
 select * from (
        select to_char(sysdate,'yyyymmdd') "date",owner,segment_name,segment_type,tablespace_name,round(sum(bytes)/1024/1024,2) Mbytes
        from dba_segments  group by owner,segment_name,segment_type,tablespace_name order by 5,4,3) a where a.Mbytes>50;
 commit;
end;
/

测试执行
SQL> exec insert_seg_sql

PL/SQL procedure successfully completed.

3）JOB创建，定时任务，每天凌晨一点执行
declare
job number;
BEGIN
DBMS_JOB.SUBMIT(
JOB =>job,
WHAT =>'insert_seg_sql;',
NEXT_DATE=>sysdate,
INTERVAL=>'TRUNC(sysdate+1)+1/24'
);
commit;
end; 
/
视图查询job信息
SQL> select job,SCHEMA_USER,LAST_DATE,NEXT_DATE,BROKEN,INTERVAL,WHAT,FAILURES from dba_jobs where what like 'insert_seg_sql%';
 JOB SCHEMA_USE LAST_DATE    NEXT_DATE    BR INTERVAL                       WHAT                   FAILURES
---- ---------- ------------ ------------ -- ------------------------------ -------------------- ----------
   1 SYS        18-MAR-19    19-MAR-19    N  TRUNC(sysdate+1)+1/24          insert_seg_sql;               0

4）执行JOB
第一次需要手动执行    
begin
   DBMS_JOB.RUN(1);
 end;
/

5）SQL查询分析
SQL>with c as ( 
select owner,segment_name,segment_type,id,tablespace_name,mbytes,lead(id,1,null) over(partition by owner,
segment_name,segment_type,tablespace_name order by id) as "lead_id",
lead(mbytes,1,null) over(partition by owner,segment_name,segment_type,tablespace_name order by id) as "lead_mbytes",
(lead(mbytes,1,null) over(partition by owner,segment_name,segment_type,tablespace_name order by id) -MBYTES) as "day_zenzhang_mb"  from seg_tab_log)  
select * from c where c.id=to_char(sysdate-1,'yyyymmdd');
OWNER      SEGMENT_NAME                   SEGMENT_TYPE                 ID TABLESPACE_NAME          MBYTES    lead_id lead_mbytes day_zenzhang_mb
---------- ------------------------------ -------------------- ---------- -------------------- ---------- ---------- ----------- ---------------
SYS        IDL_UB1$                       TABLE                  20190317 SYSTEM                      272   20190318         472             200
SYS        INDEX_A_OWNER                  INDEX                  20190317 SYSTEM                       80   20190318         280             200
SYS        RANGE_PART_TAB                 TABLE PARTITION        20190317 SYSTEM                      144   20190318         344             200
SYS        SOURCE$                        TABLE                  20190317 SYSTEM                       72   20190318         272             200
SYS        TEST01                         TABLE SUBPARTITION     20190317 SYSTEM                      224   20190318         424             200
SYS        TEST_OBJ2                      TABLE                  20190317 SYSTEM                      472   20190318         672             200
SYS        TEST_OBJ3                      TABLE                  20190317 SYSTEM                      112   20190318         312             200
XDB        SYS_LOB0000069708C00025$$      LOBSEGMENT             20190317 SYSAUX                    54.19   20190318      254.19             200

--分析函数，简单说明，lead （p1,p2,p3）【p1查询显示什么列的信息、p2查询下面第几个的信息1，代表查询该列的下一行记录、p3默认值，没有找到数值输出信息null】
                    over[partition 分组，本次中对于不同的对象，都是一条单独的记录，通过partition分组查询  order by 排列，可以升序可以降序，对id列排序后，20190317的下一行记录是20190318】

--后续操作，比如后续多个日期时，需要修改该SQL,后续持续更新