Skip to Main Content
  • Questions
  • Finding the number of rows in each table by a single sql

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, rajesh.

Asked: October 18, 2001 - 10:35 pm UTC

Last updated: December 04, 2023 - 2:17 am UTC

Version: 8i

Viewed 100K+ times! This question is

You Asked

hi tom

i have a databse with 125 tables. i can find the total number of tables of the database by the sql
select * from tab; now i would like to know the number of rows in each table of my database with out
executing select count(*) from...; each time.

please help me

sincerely
rajesh


and Connor said...

I've put together a video explaining some options and whether you should be doing this at all.






Rating

  (24 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, October 19, 2001 - 10:57 am UTC

hi tom 

i have midified get_rows

FUNCTION GET_ROWS ( p_tname in varchar2,p_own in varchar2 ) return number
as
    l_columnValue    number default NULL;
begin
    execute immediate
       'select count(*)
          from ' ||p_own||'.'||p_tname
          INTO l_columnValue;

    return l_columnValue;
end;


i only want to get row count if count(*) > 0 

and i am getting error

  1  select * from (SELECT  user, table_name,
  2           get_rows( table_name,'SIEBEL') cnt
  3        from all_tables WHERE OWNER = 'SIEBEL')
  4* WHERE   cnt > 0
SQL> /
         get_rows( table_name,'SIEBEL') cnt
         *
ERROR at line 2:
ORA-00942: table or view does not exist
ORA-06512: at "SBLINT.GET_ROWS", line 5
ORA-06512: at line 1

thanks 

Tom Kyte
October 19, 2001 - 12:35 pm UTC

Well, its obvious -- Its trying to query some SIEBEL CRM tables and my code refuses to touch their stuff (thats a joke ;)

Anyway, see

</code> http://asktom.oracle.com/Misc/RolesAndProcedures.html <code>

you must have access via a role. Try adding:

function get_rows .... return number
AUTHID CURRENT_USER
as
....


to let it run with invokers rights and roles. That should solve it.

Get Rows

Ping Cui, October 19, 2001 - 11:24 am UTC

Hi Tom,

Love your column. Just one comment. In order to be
able to use

select table_name, num_rows from user_tables;

to get the row numbers. You may have to perform table
'analyze' operation against all tables. For example,

select 'analyze table ' || table_name || ' compute statistics;' from user_tables;

Run 'analyze' statements

select table_name,num_rows from user_tables;


Ping

Tom Kyte
October 19, 2001 - 12:36 pm UTC

Well, I'd use dbms_stats to analyze but .... yes, that is true, you need to analyze the tables.

I tried to infer that with my opening statement:

"Well, if you are doing things correctly, you are using the cost based optimizer
and statistics are generally up to date. ...."



Printing

Citrus, October 19, 2001 - 6:39 pm UTC

Hi Tom,

I'm one of your fans and keep collecting your replies. While printing the questions, everything prints fine, but your Followup answers are truncated from the right, so I need to take a pen and complete the missed info. Could you please make it printable on A4.

Thanking you

Tom Kyte
October 19, 2001 - 6:50 pm UTC

I'll see what they can do with regards to that.

I need to add a where clause to the function get_rows()

Sujit, May 18, 2006 - 12:39 pm UTC

I am trying to add a where condition to the execute immediate statement in your function

execute immediate 'select count(*) from ' INTO l_rowcount || FROM l_table_name WHERE || 'SUBSTR(' || l_table_name, INSTR(l_table_name,'_', 1, 1)+1) || '_PK' > 0;

Having trouble with parsing. What am I doing wrong

Tom Kyte
May 19, 2006 - 10:03 am UTC

ugh, you GOTTA USE BINDS

and the into is "last", not in the middle


execute immediate '
select count(*)
from ' || l_table_name || '
where substr( :x, instr(:y,''_'',1,1)+1 ......'
using l_table_name, l_table_name INTO l_rowcount;


oh, i just stopped typing there cause the rest of the where clause makes no sense at all.


where substr(x,instr(x,_',1,)+1)_PK > 0

that makes no sense, I don't know what your query is meant to do.

Good

A reader, September 12, 2006 - 11:05 am UTC


Table name and its rows

Hao Zhang, October 20, 2006 - 5:16 pm UTC

it is very convenient to get answer from you.

thanks

raheem, September 21, 2011 - 10:42 am UTC

hi,
you create a function with 2 IN arguments but when your run this sql stmt

select user, table_name,
get_rows( user||'.'||table_name) cnt
from user_tables

you are only passing 1 argument, i think it's wrong.

Tom Kyte
September 21, 2011 - 12:29 pm UTC

read closer, MY get_rows takes one input.

someone else posted a MODIFIED version of it that takes two.

Done with a single SQL

Marcus, September 22, 2011 - 12:50 am UTC

I found a solution that uses only SQL, though it is slower than a PL/SQL solution

https://forums.oracle.com/forums/thread.jspa?threadID=2235623&tstart=0#9648938

Also read the blog post linkes in the OTN post

Regards
Marcus

Thanks

ramesh.maldakal, July 30, 2013 - 12:25 pm UTC

Thanks boos,nice solution.It is very useful.

A reader, October 10, 2016 - 10:25 am UTC

How to Find a length of each row in a single table?
October 11, 2016 - 12:36 am UTC

Another way to programatically count rows

Hari, November 24, 2020 - 2:29 pm UTC

Hi

I am using below for counting rows, but it's throwing table or view does not exist at line 7. Can you please guide me a better way without creation of fucntion or stored procedure on DB?

declare
v_count integer;
begin

for r in (select table_name,owner from all_tables at WHERE table_name not like'%$%' )
loop
execute immediate 'select count(*) from ' || r.TABLE_NAME into v_count;
INSERT INTO ROWCOUNT_TBL(TABLE_NAME,SCHEMA_NAME,RECORD_COUNT,CREATED)
VALUES (r.table_name,r.owner,v_count,SYSDATE);
end loop;

end;

Regards
Hari
Chris Saxon
November 24, 2020 - 4:40 pm UTC

This can access tables in other schemas, but

execute immediate 'select count(*) from ' || r.TABLE_NAME into v_count;


doesn't include the schema! So it looks for all tables in the current user. You need to add this to the query.

12c's WITH FUNCTION

Asim, August 08, 2022 - 7:17 pm UTC

The first reply by Tom in this discussion, in which he created a user defined function can now in 12c be done in single SQL without creating function but using the same function in WITH clause

WITH
function get_rows( p_tname in varchar2 ) return number
as
l_columnValue number default NULL;
begin
execute immediate
'select count(*)
from ' || p_tname INTO l_columnValue;
return l_columnValue;
end;
select user, table_name,
get_rows(user||'.'||table_name) cnt
from user_tables

right?
Connor McDonald
August 09, 2022 - 3:06 am UTC

yup, although when people run these queries I always ask:

"What do you hope to achieve?"

Hope?

Asim, August 09, 2022 - 10:15 am UTC

What you hope to achieve? Can you please explain?

One thing is performance is better than creating the udf and calling it.
Connor McDonald
August 10, 2022 - 3:19 am UTC

If I have a list of tables in my schema, and how many rows are in each, then what benefit does that give me?

How does it change how I view this database? or manage it?

I don't care if a table has 10 rows or 10billion. I care if the system is secure and performing well. That has nothing to do with the size of the tables

Maybe partitioning

Asim, August 14, 2022 - 10:40 am UTC

May be to see whichbtables are large enough to consider for partitioning
Connor McDonald
August 15, 2022 - 5:17 am UTC

select num_rows
from user_tables

will do that without smashing my server

validate an ETL process

Russell H, August 29, 2022 - 6:19 pm UTC

A valid reason to do it might be to validate an external ETL process that claims to have inserted a precise number of rows into a set of tables, and then stopped. If that process is the only one that can insert rows, the counts can be used to validate that claim. I can't think of another way to do it.
Connor McDonald
August 30, 2022 - 1:35 am UTC

Agreed.

Need to know what you(r ETL process) just did?

Peter G, August 29, 2022 - 8:09 pm UTC

SQL%ROWCOUNT may have your answer.
Connor McDonald
August 30, 2022 - 1:36 am UTC

Agreed. For any ETL process, the stronger your logging/instrumentation framework, the less hassles you have when it comes to debugging issues.

Read consistency issue

Asim, September 30, 2022 - 10:29 pm UTC


The first solution Tom gave, and the solution I (Asim) gave on 8th August 2022 ( which is just copying the tom's function in the WITH clause), and the video of connor, none of us considered or said anything about the transaction isolation level, which means we are assuming default ie statement level, due to which the solution may produce read consistency issues of SQL calling PL/SQL calling SQL mentioned here

https://blogs.oracle.com/sql/the-problem-with-sql-calling-plsql-calling-sql


Here is the query for ready referrence

WITH
function get_rows( p_tname in varchar2 ) return number
as
l_columnValue number default NULL;
begin
execute immediate
'select count(*)
from ' || p_tname INTO l_columnValue;
return l_columnValue;
end;
select user, table_name,
get_rows(user||'.'||table_name) cnt
from user_tables


So, I think we should set the transaction isolation level to serializeable or read only to get read consistency, otherwise if some other users change the no. of rows (delete or insert) in any of the tables during execution of above query, the results will not be of same point in time






count of selected table by calling single function

A reader, October 09, 2022 - 4:56 am UTC

Hi Tom,
pls help me i have a requirement ,
in my database there are 50 tables i have to validate 10 tables only for count, without writing select count(*) from table...each time
I have to place required table names in single file and parameterize that file, so whatever the table names in that file that count should i need to get
pls put your comments asap
Connor McDonald
October 09, 2022 - 2:23 pm UTC


A good reason to do count

MK, November 09, 2022 - 5:01 pm UTC

I am running Change Data Capture (Differential Extract) from Oracle to Microsoft Sql Server.
It has faults.
In order to troubleshoot, I compare the count of record on Oracle with the result count of record on MSSQL.
This gives me an idea what is wrong.
Connor McDonald
November 11, 2022 - 3:26 am UTC

In such cases though, would you not want to hash the data rather than just count the records?

Finding the number of rows in each table by a single sql

Md. Arif Hossain, June 22, 2023 - 6:51 am UTC

You can use the below query to view all rows without using count
-----------------------------------------------------------------------
SELECT TABLE_NAME, NUM_ROWS
FROM user_tables
WHERE TABLE_NAME IN (SELECT TABLE_NAME
FROM all_tables
WHERE OWNER = 'MISMIG' AND STATUS = 'VALID')
Connor McDonald
July 03, 2023 - 2:26 am UTC

True ( as long as you're prepared for an estimate).

It still remains mystery to me after all of these years why people need an exact count

version 11.2.0.4 sqlplus

A reader, December 01, 2023 - 2:40 am UTC

declare
v_count number(20);
begin
for r in (select table_name,owner from dba_tables WHERE owner <>'SYS')
loop
execute immediate 'select count(1) from '|| r.owner||'.'||r.table_name into v_count;
INSERT INTO ROWCOUNT_TBL(table_name,schema_name,record_count,created) values (r.table_name,r.owner,v_count,SYSDATE);
end loop;
end;
10 /
declare
*
ERROR at line 1:
ORA-25191: cannot reference overflow table of an index-organized table
ORA-06512: at line 6


declare
v_count number(20);
begin
for r in (select table_name,owner from dba_tables )
loop
execute immediate 'select count(1) from '|| r.owner||'.'||r.table_name into v_count;
INSERT INTO ROWCOUNT_TBL(table_name,schema_name,record_count,created) values (r.table_name,r.owner,v_count,SYSDATE);
end loop;
end;
10 /
declare
*
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at line 6


declare
v_count number(20);
begin
for r in (select table_name,owner from dba_tables where owner='SCOTT')
loop
execute immediate 'select count(1) from '|| r.owner||'.'||r.table_name into v_count;
INSERT INTO ROWCOUNT_TBL(table_name,schema_name,record_count,created) values (r.table_name,r.owner,v_count,SYSDATE);
8 end loop;
end;
10 /

PL/SQL procedure successfully completed.

SYS@nv_1<2023-12-01 10:35:46>DESC ROWCOUNT_TBL;
Name Null? Type
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------------------------------------------------------
TABLE_NAME VARCHAR2(32)
SCHEMA_NAME VARCHAR2(32)
RECORD_COUNT NUMBER(20)
CREATED DATE

SYS@nv_1<2023-12-01 10:36:00>


why?
Connor McDonald
December 04, 2023 - 2:17 am UTC

why what?

The ORA-25191 is because you are being too broad with your search.

Look at adding

SECONDARY = 'N'



ORA - 25191

NAGA, December 20, 2023 - 6:45 pm UTC

i AM USINF BELOW, WHAT I AM DOING WRONG ?

CREATE OR REPLACE FUNCTION OITLEAPUVVUN0.COUNT_ROWS (
p_TABLE_NAME IN VARCHAR2,
p_OWNER IN VARCHAR2 DEFAULT NULL,
p_EXPORT_SCN IN NUMBER)
RETURN NUMBER
AUTHID CURRENT_USER
IS
NUM_ROWS NUMBER;
STMT VARCHAR2(2000);
BEGIN
IF p_OWNER IS NULL THEN
STMT := 'SELECT /*+ PARALLEL(4) */ COUNT(*) FROM "' || p_TABLE_NAME || '" AS OF SCN ' || p_EXPORT_SCN;
ELSE
STMT := 'SELECT /*+ PARALLEL(4) */ COUNT(*) FROM "' || p_OWNER || '"."' || p_TABLE_NAME || '" AS OF SCN ' || p_EXPORT_SCN;
END IF;
EXECUTE IMMEDIATE STMT INTO NUM_ROWS;
RETURN NUM_ROWS;
END; -- FUNCTION COUNT_ROWS
/


>> SELECT OWNER, TABLE_NAME, COUNT_ROWS(TABLE_NAME,OWNER,11052875780992) ROW_COUNT
FROM DBA_TABLES
WHERE OWNER NOT IN (SELECT USERNAME
FROM DBA_USERS
WHERE ORACLE_MAINTAINED='Y'
OR USERNAME LIKE 'APEX%'
OR USERNAME in ('CRPGGADMIN'))
AND EXTERNAL = 'NO' ORDER BY OWNER
*
Error at line 1
ORA-25191: cannot reference overflow table of an index-organized table
ORA-06512: at "OITLEAPUVVUN0.COUNT_ROWS", line 16
ORA-06512: at line 1


ORA - 25191

NAGA, December 20, 2023 - 7:06 pm UTC

Never mind Figured out

SELECT OWNER, TABLE_NAME, COUNT_ROWS(TABLE_NAME,OWNER,##############) ROW_COUNT
FROM DBA_TABLES
WHERE OWNER NOT IN (SELECT USERNAME
FROM DBA_USERS
WHERE ORACLE_MAINTAINED='Y'
OR USERNAME LIKE 'APEX%'
OR USERNAME in ('CRPGGADMIN'))
AND EXTERNAL = 'NO'
AND (IOT_TYPE IS NULL OR IOT_TYPE != 'IOT_OVERFLOW');

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database