DM查询模式下所有的表和记录

工作中，我们时常统计某个模式下有哪些表，表中有多少条记录，如果每张表去统计，工作量会非常的大。介绍一种简单方便的统计方法：

方法一

1.创建一张临时表：

create global temporary table test(tab_name varchar(30),row_num int) on commit preserve rows;

2、执行语句体：

declare

count_rows int;

v_sql varchar(100);

begin

count_rows:=0;

for a in (select TABLE_NAME from dba_tables where owner='DMHR' and TABLESPACE_NAME <>'TEMP')

loop

v_sql:='select count(*) from DMHR.'||a.TABLE_NAME;

execute immediate v_sql intocount_rows;

insert into tes tvalues(a.TABLE_NAME,count_rows);

end loop;

select * from test;

end;

/

实际使用中把DMHR替换成对应的模式名。

方法二

--drop table table_count;
create table table_count (owner varchar(100),table_name varchar(100),cnt int);
declare
v_owner VARCHAR2(100);
v_tabname VARCHAR2(100);
stmt  VARCHAR2(200);
num_rows number;
begin
    for rec in (select owner,table_name from dba_tables where owner='DMHR' order by 1, 2)
    loop
        select 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;
--truncate table table_count;
select count(*) from table_count;
select * from table_count order by cnt desc;
select count(*) from dba_tables;

在迁移过程中，核对源端和目的端数据的时候极为方便。