General
cancel
Showing results for 
Search instead for 
Did you mean: 

SQL or PLSQL program for getting count(*) of tables !!

SOLVED
Go to solution
Chris Fung
Frequent Advisor

SQL or PLSQL program for getting count(*) of tables !!

Hi all,

I am planning to keep all the count(*) figures for all my tables to a statistic table. Any idea for using SQL or PLSQL to do this ??

Cheers,

Chris,
5 REPLIES
harry d brown jr
Honored Contributor

Re: SQL or PLSQL program for getting count(*) of tables !!

Is there a reason that you have such a POOR record of assigning points?

This member has assigned points to 93 of 164 responses to his/her questions.

http://forums.itrc.hp.com/cm/TopSolutions/1,,CA721374!1!questions,00.html

live free or die
harry
Live Free or Die
Wodisch_1
Honored Contributor

Re: SQL or PLSQL program for getting count(*) of tables !!

Hi Chris,

the basic idea usually is to write a SQL script which creates the SQL script you want, and then start it.
Something like

connect internal
set termout off
set feedback off
spool /tmp/count.sql
select 'select count(*) from ' || TABLE_NAME || ';' "rem cmd"
from DBA_TABLES;
spool off;
start /tmp/count.sql

HTH,
Wodisch
Brian Crabtree
Honored Contributor
Solution

Re: SQL or PLSQL program for getting count(*) of tables !!

There would be a few ways to do this. A PL/SQL statement would work, however you would have to be able to support it in the future. The following should work (caveat emptor though).
------
declare
cursor tablelist is select table_name from user_tables;
tabname varchar2(40);
sqlstatement varchar2(200);
numcount varchar2(20);
begin
open tablelist;
loop
fetch tablelist into tabname;
exit when tablelist%NOTFOUND;
sqlstatement:='select count(*) from '||tabname;
execute immediate sqlstatement into numcount;
insert into stat_table values (tabname,sysdate,numcount);
end loop;
end;
/
-----------

Brian
Jeanine Kone
Trusted Contributor

Re: SQL or PLSQL program for getting count(*) of tables !!

If you are using the cost based optimizer, you should be gathering statistics regularly. This is one of the values computed (num_rows). For a really easy method - you could always just "insert into my_stat_table select table_name, num_rows from dba_tables). This, of course, assumes you update your statistic every day before you do the insert.

Chris Fung
Frequent Advisor

Re: SQL or PLSQL program for getting count(*) of tables !!

Hey Harry,

Honestly, I am not quite aware to give points in HP forums in my past few years!! However, you can check my recent records, among the recent 6 months+, I always give points to those who answer my questions.

I really appreciate you guys spend time and effort to help me. That's the process for me to grow up, to be mature and to be fair for others.

Please be fair to me and don't just count on me in the "PAST"

Chris,