Zane Appel's Oracle Cookbook: Display the number of rows in each table

Count rows in each table:


set heading off
set feedback off
set pagesize 0
set termout off
set trimout on
set trimspool on
set recsep off
set linesize 100

column d noprint new_value date_
column u noprint new_value user_

spool tmp

select   'select '''||table_name||' : ''||count(*) from '||table_name||';',
         to_char(sysdate, 'YYYYMMDDHH24MISS') d, 
         user u
from     user_tables
order by table_name
/

Spool off
Spool count_&user_._&date_
@tmp.lst
Spool off

Count as of last analyze (faster but less accurate):


COLUMN DUMMY NOPRINT
  COMPUTE SUM OF NUM_ROWS ON DUMMY
  BREAK ON DUMMY
  select
     NULL DUMMY,
     TABLE_NAME,
     NUM_ROWS
  FROM
     ALL_TABLES;

Note that it shows num_rows as blank if you try to add a WHERE clause on owner...