This discussion is archived
9 Replies Latest reply: Feb 28, 2008 1:38 PM by 519688 RSS

How do i store the counts of all tables in my schema

514114 Newbie
Currently Being Moderated
Hi,

I want to find the metrics(number of rows in each table) of all tables in my schema.
I want to write a pl/sql block or stored when executed brings all metrics.

For example,

Dept Table
deptno count(*)
10 1
20 1
30 1
40 1

Emp Table

deptno count(*)
10 5
20 15
30 25
40 null

How do i acheive this type of task?

Thanks in advance
  • 1. Re: How do i store the counts of all tables in my schema
    SomeoneElse Guru
    Currently Being Moderated
    If estimates (based on the last time you gathered statistics) are good enough, you can do this...
    SQL> select table_name, num_rows from user_tables;

    TABLE_NAME                                 NUM_ROWS
    ------------------------------ --------------------
    BONUS                                             0
    DEPT                                              4
    EMP                                              14
    PLAN_TABLE
    SALGRADE                                          5
  • 2. Re: How do i store the counts of all tables in my schema
    Laurent Schneider Oracle ACE
    Currently Being Moderated
    XML solution :
    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
  • 3. Re: How do i store the counts of all tables in my schema
    514114 Newbie
    Currently Being Moderated
    Thanks for the reply.

    But i want to find out the count based on group by clause of columns or set of columns and find counts not just the raw counts.

    In my case user_tables does not work.

    each table has a different query with groupings.so what do i do in this case?.

    Thanks
  • 4. Re: How do i store the counts of all tables in my schema
    SomeoneElse Guru
    Currently Being Moderated
    each table has a different query with groupings.so what do i do in this case?.
    Write a separate query for each table and each grouping you want.
  • 5. Re: How do i store the counts of all tables in my schema
    Laurent Schneider Oracle ACE
    Currently Being Moderated
    group by which column? always group by deptno? in this case you can simply add the group by to my string.
  • 6. Re: How do i store the counts of all tables in my schema
    APC Oracle ACE
    Currently Being Moderated
    XML solution :
    At last, a use for XML in the database! Bravo Laurent!

    Cheers, APC
  • 7. Re: How do i store the counts of all tables in my schema
    258929 Newbie
    Currently Being Moderated
    I want to find the count of records from all different schema's tables in a database. and this query doesn't works here.

    SELECT
    table_name,
    TO_NUMBER(extractvalue(XMLTYPE(dbms_xmlgen.getxml('select count(*) c from '||table_name)),'/ROWSET/ROW/C')) COUNT
    FROM DBA_TABLES
    WHERE owner='ACH'
    ORDER BY 1;

    Can any buddy help me. is there a way without procedure or function... please help.

    Regards,
    RahulC
  • 8. Re: How do i store the counts of all tables in my schema
    Justin Cave Oracle ACE
    Currently Being Moderated
    Assuming you have appropriate permissions on those tables, and that you don't have appropriate synonyms in place, you would need to qualify the table_name with the schema name. So
    'select count(*) c from '||table_name
    would become
    'select count(*) c from '||owner||'.'||table_name
    or
    'select count(*) c from ach.' || table_name
    Justin
  • 9. Re: How do i store the counts of all tables in my schema
    519688 Explorer
    Currently Being Moderated
    but there's multiple rows coming back per table then
    simply adding the group by to the getxml() sql yields
    ORA-19025: EXTRACTVALUE returns value of only one node

    it would have to be something along these lines
    select
      xmlsequence(
          xmltype(
           dbms_xmlgen.getxml('select deptno,  count(*) c from '||table_name||' group by deptno')
          ).extract('/ROWSET/ROW/*') 
      )
    from user_tables
    where table_name in (select table_name from user_Tab_Columns where column_name='DEPTNO')
    /
    XMLSEQUENCE(XMLTYPE(DBMS_XMLGEN.GETXML('SELECTDEPTNO,COUNT(*)CF
    ---------------------------------------------------------------
    XMLSEQUENCETYPE(XMLTYPE(<DEPTNO>10</DEPTNO>
    ), XMLTYPE(<C>1</C>
    ), XMLTYPE(<DEPTNO>20</DEPTNO>
    ), XMLTYPE(<C>1</C>
    ), XMLTYPE(<DEPTNO>30</DEPTNO>
    ), XMLTYPE(<C>1</C>
    ), XMLTYPE(<DEPTNO>40</DEPTNO>
    ), XMLTYPE(<C>1</C>
    ))
    
    XMLSEQUENCETYPE(XMLTYPE(<DEPTNO>10</DEPTNO>
    ), XMLTYPE(<C>3</C>
    ), XMLTYPE(<DEPTNO>20</DEPTNO>
    ), XMLTYPE(<C>5</C>
    ), XMLTYPE(<DEPTNO>30</DEPTNO>
    ), XMLTYPE(<C>6</C>
    ))
    
    
    2 rows selected.