客户数据库，空间不够，需要实时跟进，观察哪些对象的大小在增长。
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,后续持续更新