REM
REM DBAToolZ NOTE:
REM	This script was obtained from DBAToolZ.com
REM	It's configured to work with SQL Directory (SQLDIR).
REM	SQLDIR is a utility that allows easy organization and
REM	execution of SQL*Plus scripts using user-friendly menu.
REM	Visit DBAToolZ.com for more details and free SQL scripts.
REM
REM 
REM File:
REM 	ctl_count_rec_1.sql
REM
REM <SQLDIR_GRP>UTIL</SQLDIR_GRP>
REM 
REM Author:
REM 	Vitaliy Mogilevskiy 
REM	VMOGILEV
REM	(vit100gain@earthlink.net)
REM 
REM Purpose:
REM	<SQLDIR_TXT>
REM	Counts records in all tables in the database          
REM	if specified so. Calls ctl_count_rec_2.sql            
REM	creates TEMPORARY FILE --> record_cnt.tmp             
REM	        OUTPUT    FILE --> record_count.out           
REM	+----------------------------------------------------+
REM	|  Please Note that TABLES with UNDEFINED data type  |
REM	|  !!!!     will not work with this script     !!!!  |
REM	|      Make Sure To check For their exsistance       |
REM	+----------------------------------------------------+
REM	</SQLDIR_TXT>
REM	
REM Usage:
REM	ctl_count_rec_1.sql
REM 
REM Example:
REM	ctl_count_rec_1.sql
REM
REM
REM History:
REM	08-01-1998	VMOGILEV	Created
REM
REM

set term on
set trims on

clear breaks
clear columns
set feedback off
set verify off

accept owner_in       char prompt "Enter Owner Name [Enter For All]:"
accept table_name_in  char prompt "Enter Table Name [Enter For All]:"

prompt
prompt Creating Table record_cnt ...
prompt

set term off 

drop table record_cnt_table;

create table record_cnt_table (
owner_table_name      VARCHAR2(400),
record_cnt            NUMBER);

set term on

prompt Done ...
prompt
prompt Generating Dynamic SQL(record_cnt.tmp) to execute ctl_count_rec_2.sql ...
prompt 

set term off

spool record_cnt.tmp

select    
'start c_count_rec_2.sql '||owner||'.'||table_name s_call
from      dba_tables
where     owner = nvl(upper('&owner_in'), owner)
and       table_name = nvl(upper('&table_name_in'), table_name);

spool off

set term on

prompt Done ...
prompt
prompt Running record_cnt.tmp to execute ctl_count_rec_2.sql
prompt which will populate table RECORD_CNT_TABLE
prompt with <owner.table_name> <row_count> information ...
prompt +----------------------------------------------------+
prompt |  Please Note that TABLES with UNDEFINED data type  |
prompt |  !!!!     will not work with this script     !!!!  |
prompt |      Make Sure To check For their existence        |
prompt +----------------------------------------------------+
prompt 

set term off 

@record_cnt.tmp

set term on

prompt Done !
prompt Here Is the RESULT:
prompt ******************************************************
clear columns
clear breaks
set pages 900
set head on
set feedback on

col owner_table_name format a60

select * from RECORD_CNT_TABLE
order by owner_table_name

spool record_count.out
/
spool off

prompt ========>  Created File record_count.out for your review !
prompt