Re: counting #rows in tables dynamically

Hi all:

Thank you very very much for all of your help.

Best regards,

Winnie Liu (oracle_dba_at_zdnetmail.com) wrote:
: 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.

--
Best regards, Received on Fri Sep 04 1998 - 15:27:04 CDT