Oracle查看所有表及各表行数


如何查看表的行数呢?最简单的方式就是用下面的命令:

select count(*) from tablename;

但是我们如果想看一下用户的所有表的行数呢?user_tables里有一个num_rows字段,可以查询此字段:

SQL> select table_name , NUM_ROWS from user_tables;
TABLE_NAME NUM_ROWS
 ------------------------------ ----------
 T051598
 T051599
 T051600
 T051601
 T051602
 T051603
 T051604
 T051605
 T051607
 T051608
 T051611
 T051614
 T257610
 T399510
 T399512
 T399514
 T399516
 T399520
 T399638
 T413451
 T413834
 T414483
 T414714
 T422788
 T422898
 T423588
 T423694
 T429877

为什么row_nums字段会是空的呢?
这部分表是刚刚imp至数据库中的,统计信息还没有收集,所以我们要先收集一下统计信息,使用如下语句:

analyze table xxx compute statistics;

再去查询,即可看到所有用户表的行数:

SQL> select table_name , NUM_ROWS from user_tables;
TABLE_NAME NUM_ROWS
 ------------------------------ ----------
 T051598 24930
 T051599 100999
 T051600 1461
 T051601 32380
 T051602 588
 T051603 9167
 T051604 583
 T051605 94
 T051607 92146
 T051608 205659
 T051611 222
 T051614 4530
 T257610 14550
 T399510 42543
 T399512 41468
 T399514 6219
 T399516 170623
 T399520 58
 T399638 55920
 T413451 1931
 T413834 461
 T414483 3
 T414714 38
 T422788 20266
 T422898 40421
 T423588 4652
 T423694 2
 T429877 121
普人特福的博客cnzz&51la for wordpress,cnzz for wordpress,51la for wordpress