1、创建一个table_count表用于记录统计结果

create table table_count (owner varchar(128),object_name varchar(128),cnt bigint,size_mb int,T TIMESTAMP);

2、执行以下脚本统计每张表的数据量及大小插入table_count表中

declare
v_owner VARCHAR2(128);
v_tabname VARCHAR2(128);
stmt  VARCHAR2(500);
num_rows bigint;
begin
    for rec in (
    select owner,object_name from ALL_OBJECTS where object_type='TABLE' and OWNER NOT IN ('SYS', 'SYSAUDITOR','SYSSSO','SYSJOB','CTISYS')    
    )
    loop
        select rec.owner,rec.object_name into v_owner,v_tabname from dual;
        stmt := 'select count(*) from "' || v_owner || '"."' || v_tabname || '"';
        EXECUTE IMMEDIATE stmt INTO num_rows; 
        EXECUTE IMMEDIATE 'insert into table_count values('''||v_owner||''','''||v_tabname||
        ''','''||to_number(num_rows)||''','''||table_used_space(v_owner,v_tabname)*32/1024||''',SYSDATE())';        
    end loop;
    commit;
end;

3、查询table_count表查看统计结果

select * from table_count order by cnt desc;