• Category Archives Oracle RAC
  • Database » Oracle Database » Oracle RAC
  • Counting Rows in Tables

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

    #!/bin/ksh
    export ORACLE_BASE=/.../oracle
    export ORACLE_HOME=/.../oracle/product/11.2.0/dbhome_1
    export ORACLE_HOSTNAME=...
    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='http://www.amazon.com/b/?node=1055398&tag=0202020202-20']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='http://www.amazon.com/b/?node=1055398&tag=0202020202-20']home[/az]/.../log/counts.log;
    set termout on echo on feed on trimspool off head on pages 40;
    disc;
    exit;
    ENDOFSQL
    

    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