Total record count per schema

Total record count per schema

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/&user@&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 
exit;

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

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

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; 
SPOOL off

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:

ADHOC@SF02>select count(*) from mytable;

COUNT(*)
---------
588538

ADHOC@SF02>create table t4
2  as select * from mytable;

Table created.

ADHOC@SF02>analyze table t4 estimate statistics;

Table analyzed

ADHOC@SF02>select table_name,num_rows
2  from user_tables
3* where table_name='T4'
SQL> /

TABLE_NAME            NUM_ROWS
-------------------- ---------
T4                        588643

ADHOC@SF02>analyze table t4 compute statistics;

Table analyzed.

ADHOC@SF02>select table_name,num_rows
  2  from user_tables
  3* where table_name ='T4'
SQL> /


TABLE_NAME            NUM_ROWS
-------------------- ---------
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 tdichiara@techtarget.com 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.

This was first published in January 2002

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

    Back to top