Select Count(*) Script in Oracle

Today one of the requirement came, where I have to found out the the record counts of more than 500 tables. Can you believe how much time it will take to encounter select count(*) from ... for each table !! Horrible. So I found the below solution for it and  worked for me fine.

For Example under DEBSHIS schema,I want to find out all the table count details

The above will create a script of all the tables under DEBASHIS and again run that script to collect the count of table.But problem is that for 500 tables,how could you know which count is for which table because in a single function we can added a individual column which will leads to
ORA-00937: not a single-group group function. But yes for 10 or 15 tables  I can suggest you to use the below method.

spool countscript.sql

select 'select count(*) from '||table_name||';' from dba_tables where owner = 'DEBASHIS';

spool off

Then run the script from  SQLPLUS

@countscript.sql

But for >100 tables,please use the below method to get your results.

Connect to your schema or as sysdba and use the below query to get the count of the all the tables in a schema.... Cheers !!

select table_name,
       to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) c from '||owner||'.'||table_name)),'/ROWSET/ROW/C')) as count
from all_tables
where owner = 'DEBASHIS' order by table_name asc; 

For more information please refer..

http://www.dba-oracle.com/t_count_rows_all_tables_in_schema.htm