Count rows in every table 1

[peterneve]

Hi,

I'm trying to do a simple query, which must be doable, but I can't get my head around it. I just want a query to count the number of rows in every table in a schema, with the output looking something like

table_name | row count
tablename1 | 213
tablename2 | 0
tablename3 | 50
etc...

Any ideas

 

[Dagon]

Run the following script from sql*plus:

set trimspool on
set echo off
set feedback off
set heading off
set pages 0
set linesize 500

spool tab_counts.sql
prompt set heading off
prompt set feedback off
prompt set pages 0
prompt set trimspool on
prompt spool tab_counts.txt
select 'select rpad('''||table_name||''',30)||'' | '' ||count(*) from '||table_name||';'
from user_tables
/
prompt spool off
spool off

exit

This generates a file called tab_counts.sql. Run this, also from sql*plus, and it should give you the output you want.

 

[peterneve]

Excellent! It works really well, but wasn't quite what I was expecting. I have added an @tab_counts.sql at the end of the script before the exit to get the output in one go, but is there a way to get the data into columns that are readable by, for example TOAD or PHP?

Thanks.

 

[Dagon]

You could convert the select statement into an insert statement which writes the table_name and row count to a temporary table. The temporary table can then be queried from anywhere.

 

[SantaMufasa]

Peter,

Dagon's script is excellent. But for proper performance, you should have Oracle regularly gather statistics about your tables and indexes. There are a variety of commands that you can issue that gather statistics either for the entire database, for individual schemae, or for individual tables and indexes.

One of the statistics that results from gathering statistics is NUM_ROWs:

select num_rows,table_name
  from user_tables where table_name like 'S%'
   and num_rows is not null;

NUM_ROWS TABLE_NAME
-------- --------------
    2345 SALES
       6 SAMBO
       6 SASA
       8 SAY
       6 SESS
       1 SID
       0 SRM_PROJECTS
       4 STEVE_SOURCE
       4 STEVE_TARGET
       5 STOCKS
       3 SYS_A_INVOICES
       2 SYS_B_INVOICES
      25 S_EMP

If there is no datum in NUM_ROWS for a particular table, then that means that no one has gathered statistics for that table...not a good thing from a optimiser-performance perspective. So, where statistics are null for a particular table, that is the first clue that you should gather statistics.


If you need to audit regularly the number of rows in tables, then you do not need to waste lots of processing cycles by doing full-table scans for each iteration of the query since the data already resides in NUM_ROWS of *_TABLES (i.e., USER_..., ALL_..., and DBA_...) if you are (re-)gathering statistics as often as you should.

Let us know if you have questions.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[peterneve]

Thanks Mufasa,

You script works well, but some of the databases that I have tried it on haven't got any statistics. How do I gather statistics. Is it a simple matter?

 

[SantaMufasa]

Peter,

Here are options for you to gater statistics at the database, schema, and/or table level (from withing SQL*Plus):

Section 1: Database statistics gathering:

SQL> exec dbms_stats.gather_database_stats(cascade=>true);

Section 2: Schema statistics gathering:

(for full-table scans that gather exact statistics):
SQL> exec dbms_utility.ANALYZE_SCHEMA('<schema name>','COMPUTE')

(for estimating statistics with a 10% sampling):
SQL> exec dbms_utility.ANALYZE_SCHEMA('<schema name>','ESTIMATE', estimate_percent => 10);

Section 3: Table statistics gathering:

for full-table scans that gather exact statistics):
SQL> analyze table <table_name> compute statistics;

(for estimating statistics with a 10% sampling):
SQL> analyze table <table_name> estimate statistics sample 10 percent;

Let us know your thoughts about these options.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Beilstwh]

While gathering statistics is normally a good idea, be careful on your 8i databases. If your optimizer mode is RULE, statistics are worthless. If the optimizer mode is CHOOSE and you have an application that requires RULE based logic (as we do on two servers at our shop), then gathering statistics blindly could cause performance problems. Always ask the DBA if it is a good idea to gather statistics on a specific server.

Bill
Oracle DBA/Developer
New York State, USA

 

[SantaMufasa]

True and Correct, Bill !

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]