首页 > 代码库 > Oracle查询数据库中所有表的记录数

Oracle查询数据库中所有表的记录数

方法一:
首先建立一个计算函数
 1 create or replace function count_rows(table_name in varchar2,
 2                               owner in varchar2 default null)
 3 return number
 4 authid current_user
 5 IS
 6    num_rows number;
 7    stmt varchar2(2000);
 8 begin
 9    if owner is null then
10       stmt := select count(*) from "||table_name||";
11    else
12       stmt := select count(*) from "||owner||"."||table_name||";
13    end if;
14    execute immediate stmt into num_rows;
15    return num_rows;
16 end;
然后通过计算函数进行统计
select table_name, count_rows(table_name) nrows from user_tables

获取要统计的值

方法二:
1 select t.table_name,t.num_rows from user_tables t 
查看记录数,但是num_rows存储的是上次分析后的值,不准确,要使用该方法,必须分析后才可以试用
完成的语句为
 1 declare
 2 v_tName varchar(50);
 3 v_sqlanalyze varchar(500);
 4 v_num number;
 5 v_sql varchar(500);
 6 cursor c1
 7 is
 8 select table_name from user_tables;
 9 begin
10 open c1;
11 loop
12 fetch c1 into v_tName;
13 if c1%found then
14 v_sqlanalyze :=analyze table ||v_tName|| estimate statistics;
15 execute immediate v_sqlanalyze;
16 v_sql := select NUM_ROWS from user_tables where table_name =upper(‘‘‘||v_tName||‘‘‘);
17 execute immediate v_sql into v_num;
18 dbms_output.put_line(表名: ||v_tName|| 行数: ||v_num);
19 else
20 exit;
21 end if;
22 end loop;
23 end;

 

 

Oracle查询数据库中所有表的记录数