Re: Calling all the SQL Guru's

On Fri, 09 Feb 2001 16:55:54 GMT, Mick Rice <mick_rice_at_my-deja.com> wrote:

>
>set pagesize 0;
>spool count.sql;
>select 'select count(*) from '||owner||'.'|| table_name||';' from
>all_tables;
>spool off;
>
>Can anyone come up with a single statement which does this ?
>

You're not too far off. There is another reply that has a working example.

If you don't need the *exact* number of rows, what I'd suggest is run a full database analyze. You need one of these every once in a while.

Then get the owner,table_name and number of rows from dba_tables.

As Sybrand pointed out, it's not a reliable way over time, but immediately after an analyze it should give you a good picture and it takes next to no time. The analyze will take a while. Use the packaged procedures to do analyze schema-wide, they do it using parallel query which is much faster.

Cheers
Nuno Souto
nsouto_at_bigpond.net.au.nospam
http://www.users.bigpond.net.au/the_Den/index.html Received on Fri Feb 09 2001 - 18:58:44 CST