Record count of all tables in a database in Oracle 9i

declare
  cnt number;
begin
  for x in (select owner || '.' || table_name tab from dba_tables) loop
    execute immediate 'select count(*) from ' || x.tab into cnt;
    dbms_output.put_line('Table: ' || x.tab || ', Record count: ' || TO_CHAR(cnt));
  end loop;
end;
/

If your table is recently analyzed then you can check number of rows in DBA_TABLES (ALL_TABLES, USER_TABLES)  view.
You will see number of rows at the time when the table was analyzed.

It is easier to see space occupied by all tables. Look into DBA_SEGMENTS.

You need a privilege to query a table. If you want to query all tables in the database then you need at least SELECT ANY TABLE system privilege. If you have DBA  role then you have necessary privileges.

You can write a simple program that will loop through DBA_TABLES to get owner and table_name and then dynamically count rows in the table. This can be very time consuming in large databases.

SET SERVEROUTPUT ON
DECLARE
   rowcnt PLS_INTEGER;
BEGIN
   FOR rc_dict IN (SELECT owner, table_name FROM DBA_TABLES ORDER BY owner, table_name) LOOP
      EXECUTE IMMEDIATE 'SELECT count(*) FROM '||rc_dict.owner||'.'||rc_dict.table_name INTO rowcnt;
      DBMS_OUTPUT.PUT_LINE('Table: '||rc_dict.owner||'.'||rc_dict.table_name||' has '||rowcnt||' rows.');
   END LOOP;
END;
/

JacekMycha
   

hi GGuzdziol thanks for the response. I just want to tell you that I am a little new to database related work and I am using TOAD to connect to the database. The above mentioned code looks like sql programming and i am not sure whether I can run it in toad and get the result which i can save into an excel sheet.

CREATE TABLE tabrows (tab VARCHAR2(61), rows number);

declare
  cnt number;
begin
  for x in (select owner || '.' || table_name tab from dba_tables) loop
    execute immediate 'select count(*) from ' || x.tab into cnt;
    insert into tabrows values (x.tab, cnt);
  end loop;
end;
/

And then

select *
  from tabrows;

and now You can copy&paste result into excel worksheet.

DROP TABLE tabrows; -- to cleanup when work is done

hi GGuzddziol i just want to confirm because i have to do the activity on the live server.

 So i have to create a table called tabrows and the run the following script in toad itself.

declare
  cnt number;
begin
  for x in (select owner || '.' || table_name tab from dba_tables) loop
    execute immediate 'select count(*) from ' || x.tab into cnt;
    insert into tabrows values (x.tab, cnt);
  end loop;
end;
 
So now the data will be available in the tabrows table...n i just have to select from it....please confirm whether I am correct..

Thanks a lot

Yes, You're correct - this will work for 100% if You are granted dba role (i.e. sys user; if not - see JacekMycha comment about needed priviledges - SELECT ANY TABLE or switch to all_tables/user_tables sys view to see only tables You have access to/You own).

hi yes i have the dba privilege. i am getting ORA-00904 error while creating the table. There is some problem with "rows". The "rows" text is been highlighted while running the create command in toad.

Just rename it to tabrows or anything that's not oracle-reserved keyword.

I havw run the following command after loggin in as sys user.

grant connect,resource,dba to user;

But still after creating the table when I run the script as told by you ie

declare
  cnt number;
begin
  for x in (select owner || '.' || table_name tab from dba_tables) loop
    execute immediate 'select count(*) from ' || x.tab into cnt;
    insert into tabrows values (x.tab, cnt);
  end loop;
end;

I am getting the follwing error.
ORA-01031: insufficient privileges
ORA-06512: at line 5

Due you have any idea why even after granting the dba privileges why I am getting this error.

The only reason I can think about is that role priviledges doesn't work in pl/sql; maybe it will be enough to grant connect, resource, SELECT ANY TABLE to user?

i have granted connect, resource and dba to the user..but still its not working

but have You tried with granting select any table?

yes i have granted select any table to user

PL\SQL procedures do not (cannot) use security roles, so a statement like this may work fine in SQL*Plus or TOAD:
select count(*) from &any_table

but when you try to execute that in a PL\SQL procedure it will fail, unless the owner of that table has done an explicit "select" grant to the owner of the procedure, or: if the owner of the procedure has the "select any table" system privilege.

Rather than using a dynamic SQL statement though in a PL\SQL procedure to count every table, I think a better approach is to analyze all of the tables (which should be done anyway for best performance, and this can be an automated job that runs every night or every weekend) then you can simply:
select owner, table_name, num_rows
from all_tables

set serveroutput on size 1000000
declare
  sql_stmt varchar2(1000);
  cnt number;

  cursor c1 is
  select table_name
  from sys.dba_tables;
begin
  for c1_rec in c1 loop
    sql_stmt := 'select count(*) from '||c1_rec.table_name;
    execute immediate sql_stmt into cnt;
    insert into tabrows values(c1_rec.table_name, cnt);
    dbms_output(c1_rec.table_name||','||cnt);
  end loop;
  commit;
end;
/

to markgeer:   the script given is not in the procedure...

Right, the procedure from GGudziol had this line:
execute immediate 'select count(*) from ' || x.tab into cnt;

But I shortened that to a similar statement that can execute outside of PL\SQL:
select count(*) from &any_table