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 exit;
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; 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:
[email protected]>select count(*) from mytable; COUNT(*) --------- 588538 [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> / TABLE_NAME NUM_ROWS -------------------- --------- 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> / 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 [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.