Re: [Q] how to find how many row in each table in database?

Larry Leung wrote:
> Ari Kaplan wrote:
> > What you should do is make a SQL script that creates another
> > SQL script containing all tables.
> > Try the following:
> >
> > SELECT 'select count(*) from '||OWNER||'.'||TABLE_NAME||';'
> > FROM ALL_TABLES
(Hi, Ari!)

I find it a bit more useful to do:

select 'select ''' || owner || '.' || table_name || ''' tab_name, count(*)

        from "' || owner || '"."' || table_name || '";' from dba_tables

When you run the script that this generates, it has the table name listed
as well as the number of rows in the table.         

> but i just wonder is that possible not to really read thru every row
> of every table in order to find this out.
> like does oracle maintain a row count for each table in some system
> table.

No, it doesn't. At least, not exactly ... when you analyze a table with the
COMPUTE option, Oracle will put the correct number of rows into the corresponding record of DBA_TABLES (in the NUM_ROWS column). But Oracle won't
keep this number in synch with the table; it will only modify the NUM_ROWS
column when you manually analyze the table.

You can use a standard procedure (part of the DBMS_UTILITY package) to analyze
an entire schema or the entire database at one go, but this will likely take
a LONG TIME; it will certainly take longer than selecting a count from each
table. If you really want to do this, look at the $ORACLE_HOME/rdbms/admin
directory for a script called DBMSUTIL.SQL which documents the procedures. You
are looking specifically for sys.dbms_utility.analyze_schema or sys.dbms_utility.analyze_database.

-bn
neumebm_at_hpd.abbott.com Received on Tue Aug 05 1997 - 00:00:00 CDT