Count All the Rows in all tables in a schema

Count All the Rows in all tables in a schema

 you can do it various options

Option 1. 


you can write a sql to get the out output an the then run the output to get the count for all tables .


select 'select count (*) as '||owner||'_'||table_name||' from ' ||owner||'.'||table_name||';' from all_tables tables where owner='XXX' order by 1;


Then run output manually:

select count (*) as USER1_ASP_AGREEMENT_SCHEDULE from USER1.ASP_AGREEMENT_SCHEDULE;
select count (*) as USER2_ASP_CITI_USER_ACCOUNT from USER2.USER_ACCOUNT;



Option 2. 

 This simple sql will get you all the rows count for all tables in schemas that you want.

SELECT OWNER,table_name, num_rows counter from DBA_TABLES WHERE owner in 

('USER1','USER2') ORDER BY 1,2;

sample output :

select count(*) from USER1.QP_INFO;
select count(*) from USER1.REFUND_ENTRY;

 Option 3.

1. Using DBA_TABLES+
You can gather the Database statistics using DBMS_STATS.GATHER_DATABASE_STATS; And then simply Query DBA_TABLES to sum the NUM_ROWS column grouping on OWNER column. This will yeild you the rowcounts of each of schemas.

2. Count Rows by Query+
You can gather the Table rowcount, without using DBMS_STATS, using the below script: 

select table_name,
   to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) X from '||table_name))
              ,'/ROWSET/ROW/X')) count
    from dba_tables
  where owner = 'owner'