create table MONITORING_STATISTIC
(
  id                number generated by default as identity,
  owner             VARCHAR2(128),
  table_name        VARCHAR2(128),
  num_rows          NUMBER,
  object_type       VARCHAR2(12),
  stattype_locked   VARCHAR2(5),
  max_counts        NUMBER,  --初始设置成num_rows
  last_changed_time TIMESTAMP(6)  --设置成空
);

comment on column monitoring_statistic.stattype_locked is '锁定状态';
comment on column monitoring_statistic.max_counts is 'count*历史最大值';
comment on column monitoring_statistic.last_changed_time is '插入时间';

alter table monitoring_statistic add  primary key(id);

如果要再次初始化最好truncate基表，要不然会插入重复数据
create table monitoring_statistic_XX;
truncate table monitoring_statistic;

插入初始数据
insert into monitoring_statistic(owner,table_name,num_rows,object_type,stattype_locked,max_counts) 
select b.*,b.num_rows
from (
select t.owner, t.table_name,NVL(t.num_rows,0) num_rows,a.object_type,a.stattype_locked  from dba_tables t,DBA_TAB_STATISTICS a 
where (t.num_rows <= 3 or t.num_rows = 100) and t.OWNER not IN(select username from dba_users where ORACLE_MAINTAINED = 'Y')  
and t.table_name = a.table_name and t.owner=a.owner
union
select t.owner, t.table_name,NVL(t.num_rows,0) num_rows,a.object_type,a.stattype_locked from DBA_TAB_STATISTICS a,dba_tables t
where a.stattype_locked IN ('ALL', 'DATA', 'CACHE') and a.OWNER not IN(select username from dba_users where ORACLE_MAINTAINED = 'Y')
and t.table_name = a.table_name and t.owner=a.owner) b;
commit;

col id for 999999
col table_name for a20
col partition_name for a10
col num_rows for 999999
col object_type for a20
col stattype_locked for a15
col max_counts for 999999
col last_changed_time for a30
col owner for a30
set linesize 999

select * from monitoring_statistic;

CREATE OR REPLACE PROCEDURE "ZHIHENGHOU"."PROC_GATHER_STAT_COUNTS"
as 
    t_count number; 
    t_num_rows number;
    t_tablename varchar2(50);
    t_owner varchar2(50);
    t_max_counts number;   --表历史最大count值
    t_sql varchar2(200);   --查询到的每个表的count*值
    m_count number;        --monitoring_statistic表行数
    t_cs int;           --循环计数器   
    CURSOR c1 IS select owner,table_name,num_rows,max_counts from ZHIHENGHOU.monitoring_statistic;   --把需要的字段筛选出来
begin
    select count(*) into m_count from ZHIHENGHOU.monitoring_statistic;
    t_cs := 0;
    FOR x IN c1 LOOP
        t_tablename := x.TABLE_NAME;
        t_num_rows := x.num_rows;
        t_owner := x.owner;
        t_max_counts := x.max_counts;
        t_sql :='select count(*) from '||t_owner||'.'||t_tablename;                        
        Execute immediate t_sql into  t_count ;
        if  t_count > t_max_counts then
            update ZHIHENGHOU.monitoring_statistic set max_counts=t_count,last_changed_time=sysdate where table_name=t_tablename and owner=t_owner;
            commit;
        END IF;
        t_cs := t_cs+1;
        DBMS_OUTPUT.PUT_LINE('The '||t_cs||' table is being checked,There are '||m_count||' tables in total!');
        END LOOP;
END;
/

set serveroutput on
call PROC_GATHER_STAT_COUNTS();

GRANT
　  CREATE SESSION,
    CREATE ANY TABLE,
    ALTER ANY TABLE,
　  SELECT ANY TABLE,
    INSERT ANY TABLE,
    UPDATE ANY TABLE,
    DELETE ANY TABLE
TO ZHIHENGHOU;