SQL & PL/SQL

If you are okay with counts being a little off and only accurate as of the last time you collected statistics you could query the TABLES (ALL, DBA, USER) views for the NUMROWS column.

Otherwise you need to construct SQL to query all your tables.

A XML solution.

SQL>  select table_name,
  2     to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) c from '||table_name)),'/ROWSET/ROW/C')) cnt
  3     from all_tables
  4    where owner = 'SCOTT'
  5      and table_name in ('EMP','DEPT');

TABLE_NAME                            CNT
------------------------------ ----------
EMP                                    14
DEPT                                    4 

This one is not mine. This question comes up often in this forum. And once i saw this answer. And i thought its really cool so just saved it in my Google Note Book ;)

I want sql query only.

You need to update your google note book. It doesn't take account of Index Organised Tables.

Based on answer from Laurent Schneider
http://laurentschneider.com/wordpress/2007/04/how-do-i-store-the-counts-of-all-tables.html

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
  9 where iot_type != 'IOT_OVERFLOW';

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

Edited by: BluShadow on Jul 8, 2009 12:00 PM

That is a query that was posted............

What are you really looking for?

Thanks a ton :)

Dint know the query was flawed. Have updated My Google notebook ;)

Is there any way to get the query result without using functions

Or the much simpler 11g variant:

SQL>  select table_name, trim(column_value) cnt from user_tables, xmltable((
      'count(ora:view("'||table_name||'"))'))
where table_name in ('EMP','DEPT')
/
TABLE_NAME                     CNT       
------------------------------ ----------
DEPT                           5         
EMP                            14        

2 rows selected.

In straight SQL. Hmmmmm.... I guess no

I'd prefer

... where iot_type != 'IOT_OVERFLOW' or iot_type IS NULL;

;)


Don't know how the emp table appears in your query though ....

It's not the SCOTT schema, it's a copy of emp and dept in my own schema for demonstration purposes. ;)

yeah, but with an iot_type of what??

Why would you want such a query?

You could try this if you must:

select owner, table_name, num_rows, last_analyzed from all_tables;

This is based on the last time you gathered stats for the tables.

Ah, I see your point. I must have missed something off in the copy/paste (like the "or IOT_TYPE is null") ;)

I have arranged yours :-)

conn scott/tiger
col cnt for 9999

select table_name,
(select to_number(column_value)
   from xmltable(('count(ora:view("' || table_name || '"))'))) as cnt
from user_tables
order by table_name;

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

No need for subqueries in recent versions:

SQL> select table_name,
            xmlcast (xmlquery ( ('count(ora:view("' || table_name || '"))') returning content) as int) cnt
    from user_tables
   where table_name in ('EMP', 'DEPT', 'BONUS')
order by table_name
/
TABLE_NAME                                                CNT
--------------------------------------------- ---------------
BONUS                                                       0
DEPT                                                        5
EMP                                                        14

3 rows selected.

;)

For some reason I am getting ora-932 when running this.
As I have never used XML, I don't understand what your code (and the other code) does.
I'm not using LONGs in the scheme I'm connected to, but I do use LOBs.

SQL> ed
file afiedt.buf is weggeschreven.

  1  select table_name,
  2              xmlcast (xmlquery ( ('count(ora:view("' || table_name || '"))')
 returning content) as int) cnt
  3      from user_tables
  4* order by table_name
SQL> /
    from user_tables
         *
FOUT in regel 3:
.ORA-00604: Fout opgetreden bij recursief SQL-niveau 1.
ORA-00932: inconsistente gegevenstypen: NUMBER verwacht, LONG gekregen

--------------
Sybrand Bakker
Senior Oracle DBA

What version are you on? Lobs shouldn't be a problem:

SQL> select * from v$version where rownum = 1
/
BANNER                                                                          
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production          
1 row selected.

SQL> create table t as select to_blob('9D9D81') bl from dual
/
Table created.

SQL> select table_name,
            xmlcast (xmlquery ( ('count(ora:view("' || table_name || '"))') returning content) as int) cnt
    from user_tables
   where table_name in ('T')
order by table_name
/
TABLE_NAME                                                CNT
--------------------------------------------- ---------------
T                                                           1
1 row selected.

You may also first try without XMLCAST.

I am on 11.2.0.*1*.0

And it appears to work on your dummy table, but crashes (also without using xmlcast) without limiting the tables.

--------
Sybrand Bakker
Senior Oracle DBA

Mice one MichaelS :-)

SQL> select * from v$version;

BANNER
--------------------------------------------------------
Oracle Database 11g Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> conn scott/tiger
接続されました。
SQL>
SQL> select table_name,
  2  xmlcast (xmlquery ( ('count(ora:view("' || table_name || '"))')
  3           returning content) as int) as cnt
  4    from user_tables;

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

SomeoneElse,

One question,
I am not seeing same count on select count(*) from mytable and select num_rows from user_tables where table_name='MYTABLE'.
I under stand oracle gets the num_rows when it analyses the table last time . Now the question is when do you decide to analyze the table ?Does oracle anlyze table internally ?

Thanks,
Mahesh

The code Michaels posted is non-functional, so it can not qualify as 'nice one'

----------
Sybrand Bakker
Senior Oracle DBA

Well obviously it is functional in principle, as shown! We just don't know it's complete limitations yet ... I bet it is some kind of table types which are a bit uncommon as e.g. iot tables:

SQL> select table_name, xmlquery ( ('count(ora:view("' || owner || '","' || table_name || '"))') returning content) cnt
  from all_tables
 where owner = 'SYS' and iot_name is not null
/
       *
Error at line 2
ORA-00604: error occurred at recursive SQL level 1
ORA-25191: cannot reference overflow table of an index-organized table

what should be value of &quot that is prompted 2 times?

&quot is the escaping of a double quote " in XML
You need to switch off prompting, in SQL * Plus...
set define off

Thank you. However query still having issues. Any suggestions.
SQL> select table_name, trim(column_value) cnt from dba_tables, xmltable((
'count(ora:view("'||table_name||'"))'))
where owner='USER1'
and table_name in ('TBL_PRACTICE','TBL_USER')
/
2 3 4 5 select table_name, trim(column_value) cnt from dba_tables, xmltable((
*
ERROR at line 1:
ORA-19112: error raised during evaluation:
XVM-01003: [XPST0003] Syntax error at ''
1 count(ora:view("TBL_PRACTICE"))
- ^

Try something like this ..

with data as (
 select table_name, DBMS_XMLGen.GetXMLType('select count(*) cnt from '||table_name) XML_Data
 from user_tables
 where external = 'NO' -- ignore external tables
)
select table_name, cnt
from data d
join XMLTable(
       '/ROWSET/ROW'
       passing d.XML_Data
       columns
         CNT integer path 'CNT'
     ) on 1 = 1

I think I posted SQL to get table row counts on this forum some time ago. Here is adjusted to 12.2 (column EXTERNAL was added to DBA/ALL/USER_TABLES - before we had to query DBA/ALL/USER_EXTERNAL_TABLES) and 19C where hybrid partitioning was added (if we exclude external tables we most likely want to exclude hybrid partitioned tables too)

SELECT  OWNER,
        TABLE_NAME,
        XMLCAST(
                XMLQUERY(
                         '/ROWSET/ROW/CNT'
                         PASSING DBMS_XMLGEN.GETXMLTYPE(
                                                        'SELECT  COUNT(*) CNT
                                                             FROM  "' || OWNER || '". "' || TABLE_NAME || '"'
                                                       )
                         RETURNING CONTENT
                        )
                AS NUMBER
               ) ROW_COUNT
  FROM  DBA_TABLES T
  WHERE OWNER = '&SCHEMA_NAME'
    AND TABLE_NAME NOT LIKE 'MLOG$\_%' ESCAPE '\' -- exclude materialized view logs
    AND NVL(IOT_TYPE,'NOT_IOT') NOT IN ('IOT_OVERFLOW','IOT_MAPPING') -- exclude IOT overflow and mapping
    AND TEMPORARY = 'N' -- exclude temporary tables
    AND NESTED = 'NO' -- exclude nested tables
    AND SECONDARY = 'N' -- exclude Oracle Text index tables and other "non-tables"
    AND EXTERNAL = 'NO' -- exclude external tables
    AND HYBRID = 'NO' -- exclude hybrid partitioned tables
  ORDER BY OWNER,
           TABLE_NAME
/

SY.
P.S. I hate Oracle not having a standard on yes/no indicator in data dictionary. For some columns they use Y/N for others YES/NO like we have nothing better to do than memorizing where what values are used.

I need to run tihs query on 12.1 database version and this does not work.

ERROR at line 2:
ORA-19202: Error occurred in XML processing
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_XMLGEN", line 288
ORA-06512: at line 1

Also in version 12.1, dba_tables does not have column "EXTERNAL".
the external tables show in dba_tables as well as dba_external_tables.
Is there a column in dba_tables that can identify the table as external table.

If you are on 12.1 use

SELECT  OWNER,
        TABLE_NAME,
        XMLCAST(
                XMLQUERY(
                         '/ROWSET/ROW/CNT'
                         PASSING DBMS_XMLGEN.GETXMLTYPE(
                                                        'SELECT  COUNT(*) CNT
                                                             FROM  "' || OWNER || '". "' || TABLE_NAME || '"'
                                                       )
                         RETURNING CONTENT
                        )
                AS NUMBER
               ) ROW_COUNT
  FROM  DBA_TABLES T
  WHERE OWNER = '&SCHEMA_NAME'
    AND TABLE_NAME NOT LIKE 'MLOG$\_%' ESCAPE '\' -- exclude materialized view logs
    AND NVL(IOT_TYPE,'NOT_IOT') NOT IN ('IOT_OVERFLOW','IOT_MAPPING') -- exclude IOT overflow and mapping
    AND TEMPORARY = 'N' -- exclude temporary tables
    AND NESTED = 'NO' -- exclude nested tables
    AND SECONDARY = 'N' -- exclude Oracle Text index tables and other "non-tables"
    AND NOT EXISTS (
                    SELECT  1
                      FROM  DBA_EXTERNAL_TABLES ET
                      WHERE ET.OWNER = '&SCHEMA_NAME'
                        AND ET.TABLE_NAME = T.TABLE_NAME
                   )
--    AND EXTERNAL = 'NO' -- exclude external tables
--    AND HYBRID = 'NO' -- exclude hybrid partitioned tables
  ORDER BY OWNER,
           TABLE_NAME
/

SY.
P.S. You might need to exclude materialized views if you don't want MV row counts.

@solomon-yakobson wrote:
If you are on 12.1 use

This would not have helped the original poster when he first asked the question - seeing how the first post is from July 2009, four years before the release of Oracle 12.1.
I just asked Jim Finch in the Community Feedback forum to clarify the thinking regarding "archived" threads - we'll see what the answer is.
Regards - mathguy

True, it wouldn't help the OP. But it might help OracleUser_VCOSJ who "unearthed" this topic.

SY.

Thank you for your assistance, This works in 12.1