Here is a sql script
-- Generating a report of all tables with their record counts
- Code File Name : count.sql
- Temporary File Name : tmp_count.sql (created when count.sql is executed)
- Report File Name : count.lst
- When this script is run, it creates a report ( file name count.lst) in the
- current directory which lists out the table names and their record counts.
- The output is echoed to the screen as well.
- It creates a temporary file tmp_count.sql in the current directory.
- Change the table name USER_TABLES to ALL_TABLES or DBA_TABLES to change
- the scope of the query.
set head off
set pagesize 0
set linesize 200
set feedback off
set termout off
spool tmp_count.sql
select 'set head off' from dual;
select 'set pagesize 0' from dual;
select 'set linesize 80' from dual;
select 'set feedback off' from dual;
select 'spool count.lst' from dual;
select 'select '||'''List of Tables with their Record Counts as of
'||to_char(sysdate,'DD-MON-YY HH24:MI:SS')||''''||'from dual;' from dual;
select 'select '||'''Total Records in '||owner||'.'||rpad(table_name,30,'
')||'::'||''''||', count(*) from '||owner||'.'||table_name||';'
from dba_tables
where owner not in ('SYS','SYSTEM')
/
select 'spool off' from dual;
spool off
set termout on
col count(*) format 999,999,999
start tmp_count;
Richard Slochowsky
Senior Oracle Database Administrator
Systems & Computer Technology
Richard Slochowsky
Senior Database Administrator
Systems & Computer Technology
Received on Thu Jan 28 1999 - 13:06:59 CST
