-- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -- Author : Sameer S Choudhari -- Description : Display row counts of all tables belongs to the given schema -- Call Syntax : @table_row_counts.sql -- Last Modified: 17/03/2015 -- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SET pagesize 0 linesize 220 feedback OFF verify OFF trimspool ON head OFF echo OFF TIME ON timing ON markup html ON entmap ON preformat OFF ; COLUMN table_name format a60 spool all_schema_table_row_counts.xls SELECT owner ||'.' ||table_name table_name, to_number( extractvalue( XMLTYPE( dbms_xmlgen.getxml('select /*+ parallel (' ||owner ||'.' ||table_name ||', 12) */ count(*) count from ' ||'"' ||owner ||'"' ||'.' ||'"' ||table_name ||'"')) ,'/ROWSET/ROW/COUNT')) count FROM dba_tables WHERE owner='&schema_name' ORDER BY table_name, count DESC ; spool OFF; -- Alternatively : The following are two options, you can use to get the table rows counts within the specified schema -- OPTON-1 : Execute below steps step-1 : gather SCHEMA LEVEL STATISTICS exec dbms_stats.gather_schema_stats('&schema_name', estimate_percent => dbms_stats.auto_sample_size, CASCADE => TRUE, DEGREE =>32, granularity =>'ALL'); step-2 : run below SELECT statment, num_rows FROM dba_tables contains latest ROW counts AS we freshly gathered SCHEMA LEVEL statistcs IN step-1. SELECT owner ||'.' ||table_name table_name, num_rows count FROM dba_tables WHERE owner='&schema_name' ORDER BY table_name, count DESC ; -- OPTON-2 : Execute below SQL SET LONG 9999999 longchunksize 9999999 pagesize 0 linesize 220 feedback OFF verify OFF trimspool ON head OFF echo OFF termout OFF spool och_all_table_count.SQL SELECT 'SELECT count(*), ''-->' ||table_name ||''' from ' ||owner ||'.' ||table_name ||';' FROM dba_tables WHERE owner='&schema_name' ORDER BY table_name; spool OFF SET termout ON spool och_all_table_count.LOG @@och_all_table_count.SQL spool OFF; SET linesize 80 pagesize 14 feedback ON trimspool ON verify ON head ON