Thanks for the question, rajesh.

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

Last updated: November 24, 2020 - 4:40 pm 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 Tom said...

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

The most efficient way to get a very good "ball park" number would be:

select table_name, num_rows from user_tables;

The most inefficient way is to actually count the rows in real time. This can be done with a function:

create or replace
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
/

but be prepared to wwwwwwaaaaaiiiiiittttttt......

Rating

(11 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

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

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

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

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.

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

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1856720300346322149

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

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.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Get Rows

Printing

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

Good

Table name and its rows

Done with a single SQL

Thanks

Another way to programatically count rows

More to Explore

PL/SQL demos

PL/SQL docs

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

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Get Rows

Printing

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

Good

Table name and its rows

Done with a single SQL

Thanks

Another way to programatically count rows

More to Explore

PL/SQL demos

PL/SQL docs