1、创建存储表数据的表结果
create table sysdba.table_count (owner varchar(100),table_name varchar(100),cnt int);
2、创建统计表的存储过程(上千万表,可能存在性能慢的问题)
create or replace procedure sysdba.cut_table_number AS
v_owner VARCHAR2(100);
v_tabname VARCHAR2(100);
stmt VARCHAR2(200);
num_rows number;
BEGINEXECUTE IMMEDIATE 'truncate table table_count;';for rec in (select owner,table_name from dba_tables where owner NOT LIKE 'SYS%' AND owner NOT LIKE 'CTIS%' order by 1, 2)loopselect rec.owner,rec.table_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)||''');';end loop;
end;
3、执行存储过程,进行表数据统计
CALL sysdba.cut_table_number;
4、统计数据量
select sum(cnt) from sysdba.table_count;
参考文章:Fɪʀᴇᴡᴏʀᴋs