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


Question and Answer

Connor McDonald

Thanks for the question, rajesh.

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

Last updated: November 11, 2022 - 3:26 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


and Connor said...

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


  (19 ratings)

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


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
    l_columnValue    number default NULL;
    execute immediate
       'select count(*)
          from ' ||p_own||'.'||p_tname
          INTO l_columnValue;

    return l_columnValue;

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


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> <code>

you must have access via a role. Try adding:

function get_rows .... return number

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;


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. ...."


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


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.


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.


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

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

Also read the blog post linkes in the OTN post



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


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?

v_count integer;

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


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.


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

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

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?"


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


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

Here is the query for ready referrence

function get_rows( p_tname in varchar2 ) return number
l_columnValue number default NULL;
execute immediate
'select count(*)
from ' || p_tname INTO l_columnValue;
return l_columnValue;
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?

More to Explore


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