Execute Immediate: Count Rows in All Tables

The EXECUTE IMMEDIATE statement allows you to build a statement on-the-fly. This is useful if you don't know the name of a table or the where conditions in advance. In this case, we'll get each table name in the schema and then ask for a count. Since this list will vary from time to time, the Execute Immediate makes sure we get a complete list.

Clear Screen
Prompt This will display the record count for all the tables in a schema.
Prompt Note: This does not display external tables, global temporary tables or tables in the recycle bin.
Prompt ------

Accept Schema Prompt 'Please type schema name: '
Prompt

Set Echo Off
Set Serveroutput On Verify Off

Declare
   v_Stmt    Varchar2(4000) ;
   v_Num_Rows Number ;

Begin
   Dbms_Output.Enable(1000000) ;

   For Rec In
      ( 
        Select Owner, Table_Name
          From DBA_Tables dt
         Where Owner=Upper('&Schema') 
           And Table_Name Not Like 'BIN%'                        /* not in recycle bin    */
           And Not Exists (Select '1'                            /* not an external table */
                             From DBA_External_Tables det 
                            Where dt.Table_Name = det.Table_Name
                          )
           And Temporary = 'N'                                   /* Not a temporary table */
         Order by Table_Name
      ) 

   Loop
      v_Stmt:=  'Select Count(1)' ||
                'From ' || Rec.Owner || '.' ||  Rec.Table_Name ;  

      Execute Immediate v_Stmt Into v_Num_Rows; 

      Dbms_Output.Put_Line('# Rows: '||
                           To_Char(v_Num_Rows,'999,999')
                           ||' - '||
                           Rec.Table_Name
                          );
   End Loop;
End;
/

Thanks to Rob Balter, DBA at MSA for the core logic.

See Oracle Online Documentation for further detail:
Execute Immediate