accurate row count of all tables in schema 3

[landolakes]

I am trying to get an accurate row count for all tables owned by a user at a specific moment in time. Querying the user_tables table seemed like the ideal solution... just run a dbms stats, and then query the table. However, since the num_rows columns in dba_tables and user_tables only seems accurate when a dbms_stats is run with COMPUTE option on, and therefore inaccurate when an ESTIMATE is used on dbms_stats, I cannot use that column as I had wanted.

I therefore began attemps to run real-time counts of records based on the table names in user_tables and write to another table the results. I cannot get my simple cursor loop to work.

Is there a better way to get accurate record counts or are there any suggestions to get my PL/SQL to work? Thanks!

CREATE TABLE TABLECNT
(TABLE_NAME  VARCHAR2(40 CHAR),
  NUM_ROWS    INTEGER);


DECLARE
  C_TABLE_NAME	VARCHAR2(40);
  CURSOR CNTCURS IS (SELECT TABLE_NAME FROM USER_TABLES);
BEGIN
  OPEN CNTCURS;  
  LOOP
	  FETCH CNTCURS INTO C_TABLE_NAME;
	  EXIT WHEN CNTCURS%NOTFOUND OR CNTCURS%NOTFOUND IS NULL;
	  INSERT INTO TABLECNT (TABLE_NAME, NUM_ROWS)
	  VALUES(C_TABLE_NAME, (SELECT COUNT(1) FROM C_TABLE_NAME));
  END LOOP;
END;

 

[Dagon]

DECLARE
v_count NUMBER;
type t_curs is ref cursor;
v_curs t_curs;
BEGIN
FOR C_TABLE_NAME IN (SELECT TABLE_NAME FROM USER_TABLES) loop
OPEN V_CURS FOR 'SELECT COUNT(1) FROM '||C_TABLE_NAME.TABLE_NAME;
fetch v_curs into v_count;
close v_curs;
INSERT INTO TABLECNT (TABLE_NAME, NUM_ROWS)
VALUES(C_TABLE_NAME.TABLE_NAME, v_count);
END LOOP;
END;

 

[SantaMufasa]

...and another alternative:

declare
    row_cnt number;
begin
    for x in (select table_name
                from user_tables
               order by table_name) loop
        execute immediate
            'select count(*) from '
            ||x.table_name into row_cnt;
        insert into tablecnt values(x.table_name,row_cnt);
    end loop;
end;
/

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
A                                       8
ACCOUNT                                 2
BCH_HR_VLEAVE_LIABILITY                 8
CJI_BOOK_RELEASE                        0
CJI_DAILY_POPULATION                 1295
CLIENT_SCHEDULE_LANGUAGE                2

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[landolakes]

You guys are awesome!

Both solutions worked just dandy! I guess my mind was stuck in the wrong type of loop. I will leave it a mystery as to which solution I ended up using in the end, but you both deserve stars for quick and accurate replies.

Thanks again.

 

[Beilstwh]

I am not looking for a star, but if all you want is a list... why use a work file. (code borrowed from dave)

set serveroutput on
declare
    row_cnt number;
begin
    for x in (select table_name
                from user_tables
               order by table_name) loop
        execute immediate
            'select count(*) from '
            ||x.table_name into row_cnt;
        dbms_output.put_line(rpad(x.table_name,30)||lpad(to_char(row_cnt),7)); 
    end loop;
end;
/

Bill
Oracle DBA/Developer
New York State, USA

 

[SantaMufasa]

I am not looking for a star...

But you deserve one...that is a very well taken suggestion, Bill!

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]