×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

accurate row count of all tables in schema
3

accurate row count of all tables in schema

accurate row count of all tables in schema

(OP)
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!

CODE

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;

RE: accurate row count of all tables in schema

Quote:


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;

RE: accurate row count of all tables in schema

...and another alternative:

CODE

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

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

RE: accurate row count of all tables in schema

(OP)
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.

RE: accurate row count of all tables in schema

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

CODE

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

RE: accurate row count of all tables in schema

Quote (Bill):

I am not looking for a star...
But you deserve one...that is a very well taken suggestion, Bill!

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close