How do i store the counts of all tables …

How do i store the counts of all tables …

My answer to the question above using dbms_xmlgen

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

25 thoughts on “How do i store the counts of all tables …”

  1. 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…

  2. 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?

  3. 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 :mrgreen:

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

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

  6. 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;

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

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

  9. 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;
    /

  10. The issue that Coskan raises can be avoided by adding

    AND iot_type<> 'IOT_OVERFLOW'

    to the outer query on user_tables. I stumbled across that error independently.

  11. Hi, I am facing error while running this query :-

    
    select table_name,column_name,
      to_number(extractvalue(xmltype(dbms_xmlgen.getxml(
        'select count(*) c from '||table_name||' where length('||column_name||
          ') <> length(ltrim(rtrim('||column_name||')))')),'/ROWSET/ROW/C')) count
    from user_tab_columns 
    where table_name like 'MPI_MEMGC%' 
    and table_name not like '%_H'
    order by 1
    

    my intension to find out which column/columns contain uncessary spaces in the data of all the tables in schema.

  12. you did not mention the error… I suppose it it related to trimming something that is not a character.

    Try this :

    
    SQL> create table mpi_memgc(x number, y varchar2(10), z varchar2(10))
    Table created.
    SQL> insert into mpi_memgc values (1,'bla','bli     ')
    1 row created.
    SQL> commit
    Commit complete.
    SQL> select 
       'select count(*) c from "'||table_name||'" where "'||column_name||
          '" like ''% '' or "'||column_name||
          '" like '' %''' query,
      table_name,column_name,
      to_number(extractvalue(xmltype(dbms_xmlgen.getxml(
        'select count(*) c from "'||table_name||'" where "'||column_name||
          '" like ''% '' or "'||column_name||
          '" like '' %''')),'/ROWSET/ROW/C')) count
    from user_tab_columns 
    where table_name like 'MPI_MEMGC%' 
    and table_name not like '%_H'
    and data_type = 'VARCHAR2'
    order by 1;
    
    QUERY                                                                           
    --------------------------------------------------------------------------------
    TABLE_NAME                     COLUMN_NAME                         COUNT
    ------------------------------ ------------------------------ ----------
    select count(*) c from "MPI_MEMGC" where "Y" like '% ' or "Y" like ' %'         
    MPI_MEMGC                      Y                                       0
                                                                                    
    select count(*) c from "MPI_MEMGC" where "Z" like '% ' or "Z" like ' %'         
    MPI_MEMGC                      Z                                       1
    

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>