如何在oracle中查询每个表的记录条数

[yanhong430]

要一次查询所有表的记录数啊，请指教！

[又是违规昵称]

写个存储过程就行，稍后贴出来

[yanhong430]

单条select语句查询不了吗？

[又是违规昵称]

发现pl/sql就行，我自己做的测试

SQL> declare
  2      cursor c1 is select tname from tab where tabtype = 'TABLE';
  3      RecordNumber number;
  4      sqlText      varchar(100);
  5  begin
  6   for mytable in c1 loop
  7       execute immediate 'select count(*) from '||mytable.tname into RecordNumber;
  8       dbms_output.put_line(mytable.tname||'      '||RecordNumber);
  9   end loop;
 10  end;
 11  /
 
DEPT      4
EMP      14
BONUS      0
SALGRADE      5
EMP1      16
T1      28671
 
PL/SQL procedure successfully completed

原代码也发一个，免得你去行号：
declare
cursor c1 is select tname from tab where tabtype = 'TABLE';
RecordNumber number;
sqlText varchar(100);
begin
for mytable in c1 loop
execute immediate 'select count(*) from '||mytable.tname into RecordNumber;
dbms_output.put_line(mytable.tname||' '||RecordNumber);
end loop;
end;

列没对齐，你就自己想办法吧

[又是违规昵称]

执行前在sqlplus里面用个set serveroutput on，
要不看不了回显

[又是违规昵称]

搞了个好看点的:

SQL> declare
  2      cursor c1 is select tname from tab where tabtype = 'TABLE';
  3      RecordNumber number;
  4  begin
  5      dbms_output.put_line('TABLE NAME'||'        '||'RECORD NUMBER');
  6  for mytable in c1 loop
  7      execute immediate 'select count(*) from '||mytable.tname into RecordNumber;
  8      dbms_output.put_line(mytable.tname||lpad(mytable.tname,18,chr(32))||RecordNumber);
  9  end loop;
 10  end;
 11  /
 
TABLE NAME        RECORD NUMBER
DEPT              DEPT4
EMP               EMP14
BONUS             BONUS0
SALGRADE          SALGRADE5
EMP1              EMP116
T1                T128671
 
PL/SQL procedure successfully completed

[又是违规昵称]

这下终于对齐了

SQL> declare
  2      cursor c1 is select tname from tab where tabtype = 'TABLE';
  3      RecordNumber number;
  4  begin
  5      dbms_output.put_line('TABLE NAME'||'          '||'RECORD NUMBER');
  6  for mytable in c1 loop
  7      execute immediate 'select count(*) from '||mytable.tname into RecordNumber;
  8      dbms_output.put_line(mytable.tname||rpad('    ',20-length(mytable.tname),chr(32))||RecordNumber);
  9  end loop;
 10  end;
 11  /
 
TABLE NAME          RECORD NUMBER
DEPT                4
EMP                 14
BONUS               0
SALGRADE            5
EMP1                16
T1                  28671
 
PL/SQL procedure successfully completed
 

[yanhong430]

SQL> declare
2 cursor c1 is select tname from tab where tabtype='TABLE';
3 RecordNumber number;
4 begin
5 dbms_output.put_line('TABLE NAME'||' '||'RECORD NUMBER');
6 for mytable in c1 loop
7 execute immediate 'selec count(*) from '||mytable.tname into RecordNumbe
r;
8 dbms_output.put_line(mytable.tname||rpad(' ',20-length(mytable.tname),chr
(32))||RecordNumber);
9 end loop;
10 end;
11 /
TABLE NAME RECORD NUMBER
declare
*
ERROR 位于第 1 行:
ORA-00900: 无效 SQL 语句
ORA-06512: 在line 7


请问lpc19598188，这是怎么回事啊？

[yanhong430]

我是在dos黑屏下运行

[wfqqwer5213]

select t.TABLE_NAME,t.NUM_ROWS from user_tables t

[watson110]

declare
v_table varchar(50);
v_num number;
v_sql varchar(500);
cursor c1
is
select table_name from user_tables;
begin
open c1;
loop
fetch c1 into v_table;
if c1%found then
v_sql:='select count(*) from '||v_table||'';
execute immediate v_sql into v_num;
dbms_output.put_line('表：'||v_table||' 行号： '||v_num);
else
exit;
end if;
end loop;
end;

[wiler]

analyze table tab_name estimate statistics;
之后可以用
select t.TABLE_NAME,t.NUM_ROWS from user_tables t
查看记录数

[watson110]

对于楼上要逐个表进行分析吧

[xfl039]

这个简单！不过动态SQL也是一种办法啊[Quote=引用 9 楼 wfqqwer5213 的回复:]
select t.TABLE_NAME,t.NUM_ROWS from user_tables t
[/Quote]

[watson110]

其实可以这样，先用游标对每个表进行分析，然后再将记录数取出来：

declare
v_tName varchar(50);
v_sqlanalyze varchar(500);
v_num number;
v_sql varchar(500);
cursor c1 
is
select table_name from user_tables;
begin
open c1;
loop
fetch c1 into v_tName;
if c1%found then

v_sqlanalyze :='analyze table  '||v_tName||'  estimate statistics';

v_sql := 'select NUM_ROWS  from user_tables where table_name =upper('''||v_tName||''')';

execute immediate v_sql into  v_num;
dbms_output.put_line('表名： '||v_tName||' 行数： '||v_num);
else
exit;
end if;
end loop;
end;

[watson110]

更正一下：

declare
v_tName varchar(50);
v_sqlanalyze varchar(500);
v_num number;
v_sql varchar(500);
cursor c1 
is
select table_name from user_tables;
begin
open c1;
loop
fetch c1 into v_tName;
if c1%found then

v_sqlanalyze :='analyze table  '||v_tName||'  estimate statistics';
execute immediate v_sqlanalyze;
v_sql := 'select NUM_ROWS  from user_tables where table_name =upper('''||v_tName||''')';

execute immediate v_sql into  v_num;
dbms_output.put_line('表名： '||v_tName||' 行数： '||v_num);
else
exit;
end if;
end loop;
end;

[zhangyong369]

顶 楼上

[conna]

imp导出，看log文件
很清楚

虽然笨了点

[lyf_sust]

[Quote=引用 9 楼 wfqqwer5213 的回复:]
select t.TABLE_NAME,t.NUM_ROWS from user_tables t
[/Quote]
学习了

[lchfei]

SELECT MAX(ROWNUM) FROM TBL_A;