Pavan DBA's Blog

***************************************************
script to get row count of all tables in a schema
***************************************************
#!/bin/csh

            sqlplus -s <<EOF
username/password  
spool tablecount.log
select
      table_name,
      to_number(
        extractvalue(
          xmltype(dbms_xmlgen.getxml(‘select count(*) c from ‘||table_name))
          ,’/ROWSET/ROW/C’)
          )
          count
    from user_tables order by table_name;
spool off
        exit
EOF

Note: This script is written in C shell. Do necessary changes if you are using any other shell. Do test it before executing in prod env’s