Re: Script for total number of rows

bpinaki123_at_indiatimes.com (Pinaki) wrote in message news:<96eccafc.0204180035.5fa12450_at_posting.google.com>...
> bricklen13_at_hotmail.com (Bricklen Anderson) wrote in message news:<b416ca2d.0204171406.4735cbf8_at_posting.google.com>...
> > how about this? (not very elegant I'm afraid):
> >
> > set serveroutput on 1000000
> > set termout off
> > spool count_contents.sql
> > begin
> > for x in (select table_name from user_Tables) loop
> > dbms_output.put_line('select count(*) '||x.table_name||' from '||x.table_name||';');
> > end loop;
> > end;
> > /
> > spool off
> > set termout on
> > @@count_contents.sql
> >
> >
> > bpinaki123_at_indiatimes.com (Pinaki) wrote in message news:<96eccafc.0204171012.558f7ae0_at_posting.google.com>...
> > > Hi,
> > > I am a beginner in Oracle.I need a script that will show the
> > > total number of rows in all the tables in a specific schema.I can
> > > select the "NUM_ROWS" along with the table_names from user_tables but
> > > for that I have to run "Analyze table t compute statistics" for each
> > > table and I want to avoid doing that. Is there any way I can write a
> > > PL/SQL script that I can store in the database which will retrieve the
> > > information I want...
> > >
> > > Thanks,
> > > Pinaki
>
>
> Thanks for the replies... But I am not able to figure out how to write
> a cursor using dynamic plsql to retrieve the number of rows for each
> table(I am just started with Oracle).
>
> Thanks,
> Pinaki

Pinaki, see the cooperative FAQ article: Is there a simple way to produce a report of all tables in the database with current number of rows
at url: http://www.jlcomp.demon.co.uk/faq/count_all_rows.html

It has dynamic code to select and display the counts for all tables. You can replace the dbms_output with an insert to save the results to a table, and you can modify the SQL where clause to limit the tables to specific tables, tablespaces etc .... in interest.

HTH -- Mark D Powell -- Received on Thu Apr 18 2002 - 08:33:35 CDT