Problem solve Get help with specific problems with your technologies, process and projects.

Total record count per schema

This script counts the number of records in all the tables in a particular schema in a particular instance.

This script counts the number of records in all tables in a particular schema in a particular instance:

set feedback off 
set heading off 
set verify off 
set echo off 
set pagesize 1000 
set linesize 100 
accept ORA_SID char format 'A20'- 
prompt 'For which Instance do you want to count records for : ' 
accept USER char format 'A20'- 
prompt 'Which user`s schema do you want to count records from : ' 
connect &user/&[email protected]&ORA_SID 
spool count_rec 
set termout off 
break on count(*) skip 1 
select 'prompt  type='||tabtype||' name= '||tname,'select count(*) from '||tname 
||';'from tab; 
spool off 
set heading on 
set termout on 
spool counts.txt 
start count_rec.lst 
spool off 

Reader Feedback

Vladan N. writes:

1. Oracle does not recommend the use of the TAB view, as it is obsolete since Oracle V5. Instead of querying TAB view it is better to query USER_TABLES view.

2. The TAB view contains not only tables, but also views, synonyms, and other objects. The result of this script can give us more records than we really have in our target SCHEMA. If we defined a synonym for a table we will get their row count too, may be from one other SCHEMA. If we defined views on tables we will get their row count too. So we need a restriction in the last select like: where tabtype='TABLE'.

3. If you regularly make your schema statistics, as Oracle cost-based optimizer needs it, the following script is simple and much faster. The new script runs several seconds, the proposed script may run for tens of minutes for a large database. NOTE: you have the wrong number of records only for the period elapsed since last statistic execution. If you know your database daily growth then you can evaluate the delta. The error should not be greater than 1%-2%.

BREAK on report 
COMPUTE sum of num_rows on report; 
SPOOL table_count.log 
SELECT table_name, num_rows 
   FROM user_tables 
   ORDER BY table_name; 
spool off

If you are logged with DBA privileges you can make one report for all schema you need. Note: do not count SYS and other Oracle created schemas.

COLUMN num_rows format 9,999,999,999 
BREAK on report on owner 
COMPUTE sum of num_rows on report; 
COMPUTE sum of num_rows on owner; 
SPOOL table_count.log 
SELECT owner, table_name, num_rows 
   FROM dba_tables 
-- WHERE owner in ('MY_SCHEMA_1',..,'MY_SCHEMA_n') 
-- WHERE owner not in ('SYS','SYSTEM',...) 
   ORDER BY owner, table_name; 

4. It is a bad habit to make the password equal to username. We need to prompt for a password.

Database Guy. writes: I totally agree with the Vladan (above), who says that the username and password must never be the same. But I think that is a minor issue. Also, in my 15 yrs of experience in the IT industry and more than 8 yrs with Oracle I have not seen many organizations where all the tables in all the schemas were regularly analyzed. In most places, the tables are analyzed with estimate statistics and not compute statistics option (since it is faster) to give more information to the cost based optimizer to choose a better execution path for queries. Using the num_rows parameter will be misleading in such a case where the table has been analyzed without the compute statistics option. Here is an example:

[email protected]>select count(*) from mytable;


[email protected]>create table t4
2  as select * from mytable;

Table created.

[email protected]>analyze table t4 estimate statistics;

Table analyzed

[email protected]>select table_name,num_rows
2  from user_tables
3* where table_name='T4'
SQL> /

-------------------- ---------
T4                        588643

[email protected]>analyze table t4 compute statistics;

Table analyzed.

[email protected]>select table_name,num_rows
  2  from user_tables
  3* where table_name ='T4'
SQL> /

-------------------- ---------
T4           588538

Just as a footnote: No tip will fit all user requirements to the "T" and there are always improvements possible. We should refrain from bashing.

For More Information

  • What do you think about this tip? E-mail the Editor at [email protected] with your feedback.
  • The Best SQL Server Web Links: tips, tutorials, scripts, and more.
  • Have an SQL Server tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
  • Ask your technical SQL Server questions--or help out your peers by answering them--in our live discussion forums.
  • Check out our Ask the Experts feature: Our SQL, Database Design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.

Dig Deeper on Oracle and SQL