Re: SQL EXPERTS ONLY

From: <vermaat_at_gemini.tfdl.agro.nl>
Date: 1996/08/01
Message-ID: <1996Aug1.131004.532_at_ns2>#1/1

In article <31FDF44F.1A41_at_uk.oracle.com>, Simon Stow <sstow_at_uk.oracle.com> writes:
>1. in user_tables there is a NUM_ROWS column so:
>
> select table_name, num_rows from user_tables;
>
>looks like it oughta work, but doesn't, at least not on my database -
>num_rows is null for all tables - maybe it requires something else to be
>switched on?

It does, in a way. You need to perform an ANALYZE on the table to fill that column.

>
>2. Use some PL/SQL to fetch all the table name from user_tables and then
>loop thru each selecting count(*)

I always use dynamic SQL to do this:

SPOOL cnt.sql

SELECT 'SELECT COUNT(*) FROM '||table_name||';' FROM user_tables;

SPOOL OFF SET ECHO ON
SET VERIFY ON SPOOL table_count.lis

_at_cnt

SPOOL OFF EXIT

---

| Peter J.R. Vermaat | Dutch Department of Agriculture | Wageningen |

---

| p.j.r.vermaat_at_fd.agro.nl | http://www.agro.nl/~vermaat/ |

---

|                           Flagellant in Dreamland                            |
********************************************************************************

Received on Thu Aug 01 1996 - 00:00:00 CEST