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

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

  1. Jeremy

    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. Don Burleson

    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?

  5. laurentschneider Post author

    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:

  6. Niall Litchfield

    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

  7. laurentschneider Post author

    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

  8. Coskan

    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

  10. Vadim Tropashko

    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;

  12. Vadim Tropashko

    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…

  13. Marco Gralike

    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

  15. steven zydek

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

  20. Dave Hemming

    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.

  21. Dave Hemming

    Doh! I used “less than or greater than” rather than != in my code 🙂

  23. Sai

    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.

  24. Laurent Schneider Post author

    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

  27. Pingback: Using CURSOR in Oracle 11g database to locate all matches for specifed string in DB | XL-UAT

  28. Jesus Gutierrez

    Laurent
    I’m using your approach to of your function to get the rows count plus another details i need for one Stats table report

    select table_name, XXXXr.count_rows(table_name), LAST_ANALYZED, NUM_ROWS, (XXXX.count_rows(table_name)-NUM_ROWS) Diff_rows from dba_tables where owner = ‘XXXX’;

    So there are Big big tables here and when I’m executing it blow up these error

    ERROR:
    ORA-00942: table or view does not exist
    ORA-06512: at “XXXX.COUNT_ROWS”, line 6

    So this mean the function play a key role on the performance

    Any idea or advice

  29. java-37

    Laurent, It is exceptionally astute!

    Wear, results are not put away. This inquiry simply show table name and number of lines in that table.

Comments are closed.