CHOUDHARI

-- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 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