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 Wed Sep 02 1998 - 23:56:58 CDT
