Re: How to find out number of rows of all tables in the schema?

The easiest way I have found is to first analyze the schema using analyze_schema built in, then count the num_rows from dba_tables:

SQL> exec sys.dbms_utility.analyze_schema('PAS','COMPUTE');

PL/SQL procedure successfully completed.

SQL> select table_name, num_rows from dba_tables where owner='PAS';

TABLE_NAME                      NUM_ROWS
------------------------------ ---------
BUSINESS_CALENDAR                      3
CHECKVIEW                          13068
CHECKVIEWEARNS_CODE                   18
CHECKVIEW_OVERRIDE                     2
JOBS_INPUT                          3441
MAP                                  597
MAP_PL_PROJ                           10
PAS_LOG                               12
PCPAY_MONTHLY                        365
RESULT                              3441
SYSTEM_PARAMETERS                      9

11 rows selected.

Be aware that ANALYZE_SCHEMA is a hog. Do this at night if you have a large schema and it is a production system!

• Dave

suisum_at_freenet.edmonton.ab.ca wrote:

> Hi all:
>
> How can I find out the number of rows of each table in a schema?
>
> Do I need to write a script? Do I need to store the count in a temporary
> table in order to present it nicely in a report?
>
> --
> Best regards,
Received on Thu Sep 03 1998 - 10:03:03 CDT