要一次查询所有表的记录数啊,请指教!
解决方案 »
- 关于出去数据库内重复数据的问题!
- 智能机器人5天发贴20万,囊括1783支上市股票所有的全部详细信息和在线实时分析图表,你厉害还是机器人厉害?
- 在WIN2000 SERVER下如何实现ORACLE的自动备份
- 数据库导入问题(imp命令)
- Oracle安装过程中遇到的问题,急~!
- 我的oms无法启动,总是提示“这可能是一个window内部错误或者是服务错误”!怎么回事?
- 中秋快樂,散分呀!
- 如何判断两个表中的某些记录是不同的
- 急!一个关于utl_file使用的问题!多帮忙!
- 数据库中IP一问
- oracle schema 与user
- select * into b from a 出错. 缺少关键字 怎么可能呢?
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;列没对齐,你就自己想办法吧
要不看不了回显
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
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
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,这是怎么回事啊?
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;
之后可以用
select t.TABLE_NAME,t.NUM_ROWS from user_tables t
查看记录数
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 thenv_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;
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 thenv_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;
很清楚虽然笨了点