?
Solved

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

Posted on 2006-06-06
19
Medium Priority
?
5,112 Views
Last Modified: 2008-03-17
Is there any command at the database level that will count all records in  all tables in a database and is there a way to list all the tables in a database with the count of all records in each table. The database is Oracle 9i

Thanks in advance.
0
Comment
Question by:ramana903
  • 7
  • 6
  • 2
  • +3
19 Comments
 
LVL 14

Expert Comment

by:GGuzdziol
ID: 16841023
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;
/
0
 
LVL 14

Accepted Solution

by:
GGuzdziol earned 450 total points
ID: 16841049
And to sum them:

declare
  cnt number;
  total number := 0;
  tabs number := 0;
begin
  for x in (select owner || '.' || table_name tab from dba_tables) loop
    execute immediate 'select count(*) from ' || x.tab into cnt;
    total := total + cnt;
    tabs := tabs + 1;
    dbms_output.put_line('Table: ' || x.tab || ', Record count: ' || TO_CHAR(cnt));
  end loop;
  dbms_output.put_line('Total ' || TO_CHAR(total) || ' rows in ' || TO_CHAR(tabs) || ' tables');
end;
/
0
 
LVL 3

Expert Comment

by:JacekMycha
ID: 16841065
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
   
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Author Comment

by:ramana903
ID: 16841074
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.
0
 
LVL 14

Expert Comment

by:GGuzdziol
ID: 16841141
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
0
 

Author Comment

by:ramana903
ID: 16841184
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
0
 
LVL 14

Expert Comment

by:GGuzdziol
ID: 16841216
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).
0
 

Author Comment

by:ramana903
ID: 16841267
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.
0
 
LVL 14

Expert Comment

by:GGuzdziol
ID: 16841291
Just rename it to tabrows or anything that's not oracle-reserved keyword.
0
 

Author Comment

by:ramana903
ID: 16841360
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.
0
 
LVL 14

Expert Comment

by:GGuzdziol
ID: 16841383
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?
0
 

Author Comment

by:ramana903
ID: 16841463
i have granted connect, resource and dba to the user..but still its not working
0
 
LVL 14

Expert Comment

by:GGuzdziol
ID: 16841473
but have You tried with granting select any table?
0
 

Author Comment

by:ramana903
ID: 16841538
yes i have granted select any table to user
0
 
LVL 19

Assisted Solution

by:actonwang
actonwang earned 300 total points
ID: 16842389
issue:

grant select any table to <user>;

grant insert any table to <user>;

grant alter any table to <user>;

0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 16844264
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
0
 
LVL 6

Expert Comment

by:DLyall
ID: 16844282
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;
/
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16844852
to markgeer:   the script given is not in the procedure...
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 16844874
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
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses
Course of the Month16 days, 15 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question