Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: counting #rows in tables dynamically

Re: counting #rows in tables dynamically

From: Oracleguru <oracleguru_at_mailcity.com>
Date: Fri, 04 Sep 1998 15:04:22 GMT
Message-ID: <01bdd824$e3bbcd40$a504fa80@mndnet>


Hi

The only problem is that you are bound to get wrong count if you do not analyze tables continuously.
It is good only one time after you analyze the tables.

Regards !!!

Oracleguru
www.oracleguru.net
oracleguru_at_mailcity.com

Winnie Liu <oracle_dba_at_zdnetmail.com> wrote in article <6sl7a7$mpr_at_sjx-ixn5.ix.netcom.com>...
> An easiest method to get all the number of rows on each table is to
analyze
> all of them. Then query the view: dba_tables. At the same time, you can
get
> the avg_row_len too!!
>
> Daniel Clamage wrote in message <01bdd68f$10d53ec0$ec28c9cd_at_saturn>...
> >a 3rd method would be to use dynamic sql to run count(*) on every table.
> >CREATE OR REPLACE FUNCTION numrows(
> > Ptable_name IN user_tables.table_name%TYPE)
> >RETURN NUMBER IS
> >/*
> > Program Name: Number of Rows
> > Module Name : numrows.sql
> > Written By : Daniel J. Clamage
> > Description :
> > This stored function returns the number of rows in a given
> > table. The query SELECT COUNT(*) FROM table_name is built
> > and executed dynamically. On an error (such as the table
> > name was not found) the function returns NULL.
> > Example:
> > BEGIN
> > DBMS_OUTPUT.put_line('#rows in EMP: ' ||
> > numrows('emp'));
> > END;
> > /
> > Modification:
> > V.001 02-JUN-1998 - djc - Initial release.
> > V.002 12-JUL-1998 - djc - Added cleanup, comments.
> >*/
> > dyn_c BINARY_INTEGER := DBMS_SQL.open_cursor;
> > dyn_stmt VARCHAR2(32767);
> > cnt NUMBER := 0;
> > dummy INTEGER;
> >BEGIN
> > -- build the dynamic query string and parse
> > dyn_stmt := 'SELECT COUNT(*) CNT FROM ' || Ptable_name;
> > DBMS_SQL.parse(dyn_c, dyn_stmt, DBMS_SQL.NATIVE);
> > -- need to return the column value
> > DBMS_SQL.define_column(dyn_c, 1, cnt);
> > -- execute query, get the single row and column value
> > dummy := DBMS_SQL.execute_and_fetch(dyn_c);
> > DBMS_SQL.column_value(dyn_c, 1, cnt);
> > DBMS_SQL.close_cursor(dyn_c);
> > RETURN(cnt);
> >EXCEPTION
> >WHEN OTHERS THEN
> > IF (DBMS_SQL.is_open(dyn_c)) THEN -- cleanup
> > DBMS_SQL.close_cursor(dyn_c);
> > END IF;
> > RETURN(NULL);
> >END;
> >/
> >
> >Then write a simple pl/sql block to run this routine for every table in
the
> >schema:
> >declare
> > cnt number := 0;
> >begin
> > for t in (select table_name from user_tables) loop
> > cnt := cnt + numrows(t.table_name);
> > end loop;
> > dbms_output.put_line('total #rows in schema: ' || cnt);
> >end;
> >/
> >
> >--
> >- Dan Clamage
> >http://www.telerama.com/~dclamage
> >If you haven't crashed the Server,
> >you haven't been trying hard enough.
>
>
>
Received on Fri Sep 04 1998 - 10:04:22 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US