Re: Count # of records

What a dummy I am! There was a nice brain lapse on my part. Never even thought of what you suggested.

select sum(num_rows),table_name
from all_tables
where owner = 'PROD'
group by table_name;

Thank you Daniel for reminding me that I just offered the hard way of doing things.

;-)

Brick

No point in even trying my first post, it doesn't do what it's supposed to anyways...

Daniel Morgan wrote:
>
> 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 - 11:19:26 CDT