topic Re: SQL or PLSQL program for getting count(*) of tables !! in Operating System - HP-UX

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

Chris Fung — Tue, 16 Jul 2002 08:32:43 GMT

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,

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

harry d brown jr — Tue, 16 Jul 2002 10:39:27 GMT

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

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

Wodisch_1 — Tue, 16 Jul 2002 16:51:04 GMT

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

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

Brian Crabtree — Tue, 16 Jul 2002 21:22:33 GMT

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

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

Jeanine Kone — Tue, 16 Jul 2002 22:16:39 GMT

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.

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

Chris Fung — Wed, 17 Jul 2002 02:10:52 GMT

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,