Counting all of the rows in a schema can require code that
actually counts the table rows, and it's hard because rows are
constantly being added and deleted from the schema.
So, how do you count up all of the rows for all tables in a schema?
There are two sources of row counts:
- The num_rows column in dba_tables, current
only to the date-time of the last analyze with dbms_stats.
- The "real" current row count, which requires that you actually
issue SQL to count the rows.
To count all of the rows in real time, a simple SQL*Plus script will
suffice:
spool runme.sql
select 'select count(*) from '||table_name||';' from
dba_tables where owner = 'XXX';
spool off
@runme
A less intensive approach for estimating the total number of rows in
a schema would query the num_rows column, much faster:
compute sum of counter on report;
select
table_name,
num_rows counter
from
dba_tables
where
owner = 'XXX'
order by
table_name;
You can also write PL/SQL procedures to count up the number of rows
in a schema.
