Laurent Schneider

How do i store the counts of all tables …

My answer to the question above using dbms_xmlgen

SQL&gt; select
  2    table_name,
  3    to_number(
  4      extractvalue(
  5        xmltype(
  6 dbms_xmlgen.getxml('select count(*) c from '||table_name))
  7        ,'/ROWSET/ROW/C')) count
  8  from user_tables;

TABLE_NAME                      COUNT
------------------------------ ------
DEPT                                4
EMP                                14
BONUS                               0
SALGRADE                            5

This entry was posted on Wednesday, April 18th, 2007 at 16:40 and is filed under oracle, sql, xml. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.
Post to : del.icio.us - Digg it - Furl - reddit - Spurl

Put your code in <code> and </code> tags

17 Responses to “How do i store the counts of all tables …”

Jeremy Says:
April 18th, 2007 at 17:31
Hey that’s a really cool bit of SQL. Don’t think I would have thought to use dbms_xmlgen to get the whole thing into a single statement like that.

Any way you can think of off the top of your head to do the same thing (in a single statement) without using dbms_xmlgen? I’ll probably be thinking about this all day…
Don Burleson Says:
April 18th, 2007 at 22:24
Hi Laurent,

I must be getting senile, as I would think that this simple query would work:

create table t1 as
select owner, count(*) tabcnt from dba_tables;

In what table does your query store the results?

Am I missing something?
Paweł Barut Says:
April 18th, 2007 at 22:31
Laurent, It is very clever!
Don, results are not stored. This query just display table name and number of rows in that table.

Paweł
Michael Moore Says:
April 18th, 2007 at 23:10
That was just way too cool!

laurentschneider Says:
April 19th, 2007 at 07:53

Any way you can think of off the top of your head to do the same thing (in a single statement) without using dbms_xmlgen? I’ll probably be thinking about this all day…

sure. with plsql

create or replace function count_rows(table_name varchar2) 
return varchar2 is
i number;
r sys_refcursor;
begin
open r for 'select count(*) c from '||table_name;
fetch r into i;
return i;
end;
/

select table_name,count_rows(table_name) from user_tables;

it even seems plsql will perform faster

Niall Litchfield Says:
April 19th, 2007 at 10:13
Don,

Laurent is returning the rowcount for each table, your query unless I’m misreading it badly would store the number of tables in dba_tables against each table_name.

Niall
laurentschneider Says:
April 19th, 2007 at 11:24
I guess it will return ORA-00937: not a single-group group function

note that idea is to return a number of rows per table not a number of tables per owner
Coskan Says:
April 19th, 2007 at 14:34
It is so cool but does not work for IOT

SYS@XE> select
2 table_name,
3 to_number(
4 extractvalue(
5 xmltype(
6 dbms_xmlgen.getxml(’select count(*) c from ‘||table_name))
7 ,’/ROWSET/ROW/C’)) count
8 from user_tables;
ERROR:
ORA-19202: Error occurred in XML processing
ORA-25191: cannot reference overflow table of an index-organized table
ORA-06512: at “SYS.DBMS_XMLGEN”, line 176
ORA-06512: at line 1
Vadim Tropashko Says:
April 24th, 2007 at 01:34
Wouldn’t table function solution cleaner than cursor? (I’m not even considering XML solution as a contender).
Vadim Tropashko Says:
April 24th, 2007 at 01:42
Opps, your function is scalar which is simpler than table function. What I meant was that no cursor is necessary:

create or replace function count_rows(table_name varchar2)
return varchar2 is
i number;
begin
execute immediate ’select count(*) c from ‘||table_name into i;
return i;
end;
/

select count_rows(table_name) from user_tables;
laurentschneider Says:
April 24th, 2007 at 08:51
thanks vadim, execute immediate ’select …’ into is easier and cleaner than my refcursor
Vadim Tropashko Says:
April 25th, 2007 at 22:45
Mine was a minor comment, really:-) As you may have noticed, I’m on my anti-cursor crusade. Much more important is the idea summarized by “No XML!” emblem at my home page…
Marco Gralike Says:
May 3rd, 2007 at 07:55
The scalar problem can be surpassed by using extract (object_value,’/ROWSET/ROW/C/text()’). A disadvantage of both is that the performance will degrade due to DOM validation, BUT…

GREAT EXAMPLE Laurent. I love it!

Grz

Marco
Saji Alexander Says:
May 7th, 2007 at 14:59
Simply Great.
steven zydek Says:
April 24th, 2008 at 18:20
This is my version w/o the cool XML stuff that Laurent has. This pretty much just dumps out all non SYS-owned tables from dba_objects and has error handling for TABLEs that can’t be counted (i.e., the IOT exception). Let me know your thoughts. This is crude at the moment, but you get the idea.

set serveroutput on
declare
type tablename is table of dba_objects.object_name%type
index by binary_integer;
table_list tablename;
indx number := 0;
v_count number := 0;
v_tab varchar2(256);
v_sqltext varchar2(1000);
begin
for r_c3 in (select owner,object_name from dba_objects where object_type = ‘TABLE’) loop
table_list(indx) := r_c3.owner||’.”‘||r_c3.object_name||’”‘;
indx := indx + 1;
end loop;
for xndx in table_list.first..table_list.last loop
if table_list.exists(xndx)
and table_list(xndx) not like ‘SYS.%’ then
v_tab := table_list(xndx);
v_sqltext := ’select count(*) from ‘|| table_list(xndx);
— dbms_output.put_line(v_sqltext);
BEGIN
execute immediate v_sqltext into v_count;
dbms_output.put_line(table_list(xndx) || ‘, ‘ || v_count);
exception when OTHERS then dbms_output.put_line(table_list(xndx) || ‘, NULL’);
END;
end if;
end loop;
end;
/
Laurent Schneider Says:
April 24th, 2008 at 18:43
did you check my former comment about a plsql solution?
Brian Tkatch Says:
April 29th, 2008 at 16:14
Just got to this from the forums.

Laurent, that is fantastic.

Use <code> and </code> to post code

How do i store the counts of all tables …

17 Responses to “How do i store the counts of all tables …”

Leave a Reply