Re: Count # of records

You are making this more difficult than it need be:

No version number, of course, so I'm going to guess that you have Oracle 8i.

Just perform the following steps:

1. Analyze the schema which you should be doing regularly anyway for the Cost Based Analyzer. (This will load the column num_rows in the data dictionary views). To analyze the schema execute the following:

SQL> exec dbms_stats.gather_schema_stats(OWNNAME => some_schema_name, CASCADE => True);

2. Then execute the following SQL statement:

SELECT SUM(num_rows)
FROM user_tables;

or

SELECT SUM(num_rows)
FROM all_tables
WHERE owner = 'some_schema_name';

Daniel Morgan

Robert wrote:

> Hi all,
>
> I'm not that experienced in writing PL/SQL code and now I have to write a
> PL/SQL procedure to count the number of records in a given schema.
> So I created a cursor that retrieves all table names for a given schema,
> like ex. below
> CURSOR snaps_to_run IS
> SELECT s.owner || '.' || s.table_name as get_table_names
> FROM all_tables s
> WHERE s.owner=in_schema_name
> ORDER BY s.table_name
>
> In a loop I want to count the number of records for each table, and that's
> were it all goes wrong. I can't just say "select count(*) into variable from
> get_table_names, because it can't resolve the actual table name. Apparently
> I have to bind the real value.
>
> Can anyone help me with this?
>
> Thanks in advance,
>
> Robert.
Received on Mon Apr 08 2002 - 10:56:19 CDT