如何监控ORACLE数据库表的增长量:
create or replace view get_data_forall_table as
select to_char(sysdate, 'yyyy-MM-dd') riqi,
owner,
segment_name,
count(t.segment_name) as nus,
sum( bytes / 1024 / 1024 ) as Mb
from dba_segments t
where segment_type like 'TABLE%'
and owner in ('LCAM_ZC', 'LCAM_SC','LCAM_SYS')
group by owner,segment_name
收集所有用户的对象的大小。
select * from GET_DATA_FORALL_TABLE t
将所有的对象大小插入一个表中:
create table top_get_table_data as select riqi,owner,segment_name,MB from GET_DATA_FORALL_TABLE;
select * from top_get_table_data
创建一个存储过程,进行查询插入:
create or replace procedure JOB_GET_ALLDATA as
begin
insert into top_get_table_data value select riqi,owner,segment_name,MB from GET_DATA_FORALL_TABLE ;
commit;
end;
创建JOB,每天执行一次。
declare get_alldata_job number;
begin
dbms_job.submit(get_alldata_job,'JOB_GET_ALLDATA;',sysdate,'sysdate+1');
commit;
end;
查看JOB的状态
select * from dba_jobs
然后就可以看统计结果,写出统计分析的SQL进行表大小增长量统计
select * from GET_DATA_FORALL_TABLE t
select TB1.riqi,tb1.table_name,tb1.mb,TB2.riqi,tb2.table_name,tb2.mb,tb2.mb-tb1.mb,tb2.owner from
(select * from top_get_table_data tt where to_date(tt.riqi,'yyyy-mm-dd') >= to_date(to_char(sysdate-10,'yyyy-mm-dd'),'yyyy-mm-dd')) TB1,
(select * from top_get_table_data tt where to_date(tt.riqi,'yyyy-mm-dd') >= to_date(to_char(sysdate-9,'yyyy-mm-dd'),'yyyy-mm-dd')) TB2
where tb1.table_name=tb2.Table_name
and to_date(TB2.riqi,'yyyy-mm-dd') = to_date(TB1.riqi,'yyyy-mm-dd')+1
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28869493/viewspace-2137425/,如需转载,请注明出处,否则将追究法律责任。