How do I produce a report of all tables in the database with current number of rows ? ?

The Oracle (tm) Users' Co-Operative FAQ

How do I produce a report of all tables in the database with current number of rows ?

With version 8.1 and the execute immediate statement there is a fairly easy way to create a list of tables and counts. Here is a code that will perform the task for 10 tables belonging to one user. By modifying the cursor where clause it can be made to run for all tables in the database. Personally I would avoid counting the dictionary base tables (sys owned tables) and on systems with large partitioned tables you might want to consider just using the num_rows column of dba_tables if an exact count is not truly necessary. Naturally if you use this second method then it is best to collect the data immediately after analyzing.

Here is the result set for the sample code:

UT1> @ct_all
Table OWNERX.ACS_APPL_SERIES1               count is           320
Table OWNERX.ACS_APPL_SERIES2               count is           317
Table OWNERX.ACS_GEN                        count is             2
Table OWNERX.ACS_INPUT                      count is            43
Table OWNERX.ACS_OUTPUT                     count is            42
Table OWNERX.ACS_PWM_CONFIG                 count is            10
Table OWNERX.ACS_TRANS                      count is            11
Table OWNERX.AC_CURRENCY_MATRIX             count is            26
Table OWNERX.AC_CURRENCY_MATRIX_ACCT        count is            26
Table OWNERX.AC_DEPT_ACCT                   count is             0
PL/SQL procedure successfully completed.
  1  declare
  2  --
  3  --  Anonymous pl/sql code to count rows in tables of interest for v8.1+
  4  --
  5  --  basic logic
  6  --    create list of target tables (cursor)
  7  --    while more tables in list
  8  --      dynamically generate select count
  9  --      print or store results
 10  -- ---------------------------------------------------------------------
 11  --
 12  -- 20010807  Mark D Powell   Skeleton for capturing table counts
 13  --
 14  v_ct       number        := 0 ;
 15  v_sqlcode  number        := 0 ;
 16  v_stmt     varchar2(90)       ;
 17  --
 18  --         modify cursor select for tables of interest, order by.
 19  --
 20  cursor c_tbl is
 21    select owner, table_name
 22    from   sys.dba_tables
23 where  owner = 'OWNERX'
24    and    rownum < 11;
25  --
26  r_tbl      c_tbl%rowtype;
 27  --
 28  begin
 29  open c_tbl;
 30  loop
 31    fetch c_tbl into r_tbl;
 32    exit when c_tbl%notfound;
 33    v_stmt := 'select count(*) from '||r_tbl.owner||'.'||r_tbl.table_name;
 34    execute immediate v_stmt into v_ct;
 35    v_sqlcode := SQLCODE;
 36    if v_sqlcode = 0
 37  --        An insert into a row count history table should probably be here
 38       then dbms_output.put_line('Table '||r_tbl.owner||'.'||
 39                                  rpad(r_tbl.table_name,30)||
 40                                 ' count is '||to_char(v_ct,'999999999990')
 41                                );
 42       else dbms_output.put_line('Bad return code'||v_sqlcode||
 43                                 ' on select of '||r_tbl.owner||
 44                                 '.'||r_tbl.table_name
 45                                );
 46    end if;
 47  end loop;
 48  close c_tbl;
 49* end;

Here is the same data based on the static optimizer statistics. Notice the non-analyzed tables have null for number of rows. This is very simple but not necessarily very accurate means of obtaining the data.

UT1> select owner, table_name, num_rows
  2  from   sys.dba_tables
  3  where  owner = 'OWNERX'
  4  and    rownum < 11;
OWNER                          TABLE_NAME                       NUM_ROWS
------------------------------ ------------------------------ ----------
OWNERX                         ACS_APPL_SERIES1                      314
OWNERX                         ACS_APPL_SERIES2                      314
OWNERX                         ACS_GEN                                 1
OWNERX                         ACS_INPUT                              42
OWNERX                         ACS_OUTPUT                             36
OWNERX                         ACS_PWM_CONFIG
OWNERX                         ACS_TRANS                              11
OWNERX                         AC_CURRENCY_MATRIX
OWNERX                         AC_CURRENCY_MATRIX_ACCT
OWNERX                         AC_DEPT_ACCT                            0
10 rows selected.

Prior to the availability of the execute immediate statement you can substitute the dbms_sql package for the execute immediate.

Further reading: How do I count the number of rows in a table ?