主要实现功能：
定期统计数据库中所有用户的表的大小及行数增长情况。 当然也可以指定某些表
1.创建统计表
----创建统计表
create table t_zhong_tb(user_name varchar2(30),
table_name varchar2(30),
size_mb number,
num_rows number,
change_date date default trunc(sysdate));
select * from t_zhong_tb;
2.创建存储过程，插入统计数据
-----插入统计数据前，一般需要先收集统计信息，如果有几张表需要统计更加精确，请在每次插入统计数据之前完成统计信息的收集
create or replace procedure p_zhong_tb as
begin
insert into t_zhong_tb
(user_name, table_name, size_mb, num_rows)
with t3 as
(select t1.owner,
t2.table_name,
round(t1.blocks*8/ 1024, 2) size_M,
t2.num_rows
from dba_segments t1, dba_tables t2
where t1.segment_name = t2.table_name
and t1.owner = t2.owner
and t1.segment_type = 'TABLE'
and t1.owner in
(select username
from all_users
where created > to_date('20160901', 'yyyymmdd'))
and t2.num_rows > 0),
t4 as
(select a.owner,
a.table_name,
-- a.column_name,
-- b.segment_name, b.segment_type ,
ROUND(sum(b.BYTES) / 1024 / 1024, 2) size_M
from dba_lobs a, dba_segments b
where (a.segment_name = b.segment_name and a.owner not like '%SYS%')
or (a.index_name = b.segment_name and a.owner not like '%SYS%')
group by a.owner, a.table_name)
select t3.owner,
t3.table_name,
t3.size_m + nvl(t4.size_m, 0) size_Mb,
t3.num_rows
from t3, t4
where t3.table_name = t4.table_name(+)
and t3.owner = t4.owner(+);
end p_zhong_tb;
----可以先测试一下
begin
-- Call the procedure
p_zhong_tb;
end;
select * from t_zhong_tb;
truncate table t_zhong_tb;
3.定义JOB收集统计数据
----建议放在周末或业务不忙时进行
-------我这里定义在每周6中午12点进行。 Job可以是dbms_job也可以设置schedules，这里用dbms_jobs示列
begin
sys.dbms_job.submit(job => :job,
what => 'p_zhong_tb;',
next_date => to_date('10-11-2016 13:27:45', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'TRUNC(next_day(sysdate,7))+12/24');
commit;
end;
/
----这里创建好后，就会自动跑一次job，可以查看job运行情况
select * from user_jobs;
select /*+rule*/ * from dba_jobs_running;
----跑完之后，可以看到下一次执行时间为12号，也就是这个周六
4.关于查询！！！
----前面这些都只是准备工作，一切都是为了做查询，方便了解表的更新情况
4.1 查询一：查看最近5次更新情况
select *
from (select user_name,
table_name,
size_mb,
num_rows,
change_date,
row_number() over(partition by user_name, table_name order by num_rows desc, size_mb) rn
from t_zhong_tb)
where rn < 5;
4.2 查询二：查看最近5次变化过的情况
select *
from (select user_name,
table_name,
size_mb,
num_rows,
change_date,
rank() over(partition by user_name, table_name order by num_rows desc, size_mb) rn
from t_zhong_tb)
where rn < 5;
4.3 查询三：查看历史记录中行最多的表
select * from t_zhong_tb order by num_rows desc, size_mb desc;
4.4 查询四：查看最近30天的增长情况
select user_name,
table_name,
max(size_mb) - min(size_mb) h_size,
max(num_rows) - min(num_rows) h_rows,
min(change_date),
max(change_date)
from t_zhong_tb
where change_date > trunc(sysdate) - 30
group by user_name, table_name
order by 4 desc, 3 desc;
4.5 查询五：rows增长率
----过去30天的行数增长率情况
select user_name,
table_name,
max(num_rows),
min(num_rows),
max(num_rows) - min(num_rows) h_rows,
round((max(num_rows) - min(num_rows)) / max(num_rows) * 100, 2) "h_rows%",
min(change_date),
max(change_date)
from t_zhong_tb
where change_date > trunc(sysdate) - 30
and num_rows > 0
group by user_name, table_name
order by 6 desc;
4.6 查询六：size增长率
----过去30天的表大小增长率情况
select user_name,
table_name,
max(size_mb),
min(size_mb),
max(size_mb) - min(size_mb) h_size,
round((max(size_mb) - min(size_mb)) / max(size_mb)*100,2) "h_size%",
min(change_date),
max(change_date)
from t_zhong_tb
where change_date > trunc(sysdate) - 30
and size_mb > 0
group by user_name, table_name
order by 6 desc;
4.7 查询七：其他查询
5.清除数据
-----如果表中更新的数据特别大后，有的历史数据不在需要，可以直接清除
delete t_zhong_tb where change_date < sysdate-60;
alter table t_zhong_tb move; ----如果创建了索引记得重建索引
---- drop table t_zhong_tb purge;