【Oracle实战经验三】：所有表记录和数据量查询

查询当前用户下的所有表

select t.table_name from user_tables t

查询用户下所有表和表中的数据条数

1. 计算表的数据条数
   create or replace function count_rows(table_name in varchar2,
   owner in varchar2 default null)
   return number authid current_user IS
   num_rows number;
   stmt varchar2(2000);
   begin
   if owner is null then
   stmt := ‘select count() from “’ || table_name || '”’;
   else
   stmt := 'select count() from "’ || owner || ‘"."’ || table_name || ‘"’;
   end if;
   execute immediate stmt
   into num_rows;
   return num_rows;
   end;

2. 查询表中的数据条数
   select table_name, count_rows(table_name) nrows from user_tables

查询用户下所有表数据条数的总和

select SUM(nrows) from
(
select table_name, count_rows(table_name) nrows from user_tables
)