Counting Rows in Tables

For tables and materialized views (avoid materialized views by removing them from dba_mviews):

export ORACLE_BASE=/.../oracle
export ORACLE_HOME=/.../oracle/product/11.2.0/dbhome_1
export ORACLE_SID=[...]
/.../oracle/product/11.2.0/dbhome_1/bin/sqlplus user/passwd < /usr/home/.../log/counts.out 2<&1
set sqlp '' termout off echo off feed off trimspool on head off pages 0 lines 256;
spool /usr/[az url='']home[/az]/.../log/counts.log;
select 'SELECT '''||t.table_name||','', TO_CHAR(count(*),''999,999,999,990''), '||decode(mv.master,null,''' ''','''mvlog''')||' from '||t.table_name||';' from user_tables t left outer join user_mview_logs mv on(t.table_name=mv.master) 
where t.table_name not like '%$%'
order by t.table_name;
spool off;
set termout on colsep '|'
@@/usr/[az url='']home[/az]/.../log/counts.log;
set termout on echo on feed on trimspool off head on pages 40;

Remove mviews something like this:

select table_name from user_tables where table_name not in(select mview_name from user_mviews) order by 1;

And you can count the statistics but those can be well out of date because eventually it's no longer worth for the database to fully count all the rows in a table:

select table_name,num_rows,last_analyzed from user_tables order by 1;

First published 2011/08/29