关于统计数据库中所有表的更新情况——size及rows变化

 

主要实现功能：

定期统计数据库中所有用户的表的大小及行数增长情况。 当然也可以指定某些表

 

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;  

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30130773/viewspace-2128257/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30130773/viewspace-2128257/