• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2900
  • Last Modified:

List tables with count of rows in Oracle database

I am just the user of a Oracle database.  I want to list all table and row count for the database.
  select  table_name from dba_tables where Owner = 'Owner'
Is there any simple SQL query can pull that information?
If I want to display each columns distinct count , what is the query  too.

I want the output look like this:
TABLEA     12
TABLEB     15
TABLEC     40

And column distinct count
TABLEA    COLA    12
TABLEB    COLA    15
TABLEC    COLB    40

1 Solution
you can use PL/SQL

set serveroutput on
cursor c1 is select table_name from user_tables;
a varchar2(10000);
b number;
for i in c1 loop
execute immediate 'select count(*)  from '||i.table_name into  b;
if b=0 then
end if;
end loop;
To get an approximate row count by table, first gather statistics then

select table_name,num_rows from dba_tables where owner = 'OWNER';

If an approximation isn't good enough then try this...

select table_name,
to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) X from '||table_name))
          ,'/ROWSET/ROW/X')) count
from dba_tables where owner = 'OWNER';
to get a list of counts by column by table...

note, not all column types are able to be used in a count(distinct xxx)  so you have to restrict them in your where clause

SELECT   table_name,
                       xmltype(DBMS_XMLGEN.getxml(   'select count( distinct '
                                                  || column_name
                                                  || ') X from '
                                                  || table_name)),
  FROM   user_tab_cols
 WHERE   data_type IN ('NUMBER', 'VARCHAR2', 'CHAR', 'DATE');
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

note you can get approximations of counts with statistics also

select table_name,column_name, num_distinct from user_tab_cols
Mark GeerlingsDatabase AdministratorCommented:
You don't need PL\SQL to just get a list of tables and the count of rows for each.  A simple SQL query can do that easily (and much more efficiently the the PL\SQL example):

select owner, table_name, num_rows
from dba_tables order by owner, table_name;

Note, the "num_rows" column is not a real-time count, it is the count that was determined the last time that particular table was analyzed.  So, depending on when your tables were last analyzed, these results may or may not be up-to-date.

The simple query from sdstuber will quickly and efficiently get the results for each column (also based on when each table was last analyzed).
"quickly" is somewhat subjective.  Big tables will still take a long time to count.  but it should be more efficient than pl/sql for sure.

The counts by columns will likely take a long time too unless a particular user has very few tables.
Mark GeerlingsDatabase AdministratorCommented:
This was the query I meant when I said "quickly":

select table_name,column_name, num_distinct from user_tab_cols

I just tested that, and that includes views (which have null values for "num_distinct") so you may want to add this "where" clause:

where num_distinct is not null
oh that one, yes both of the queries that are statistics based will run quickly.
Of course, the gathering of the statistics may take a long time but hopefully there is already a process in place keeping the stats reasonably up-to-date.
johnnywong71Author Commented:
I think I cannot run the XML process.

Here is the error :
ERROR [HY000] [Oracle][ODBC][Ora]ORA-19202: Error occurred in XML processing
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_XMLGEN", line 176
ORA-06512: at line 1
Mark GeerlingsDatabase AdministratorCommented:
That looks like a different question or problem than your original question here.

The ORA_00942 error does not always mean "table or view does not exist" (even though that's what it says).  Often it really means: "the table or view you are trying to use DOES exist, but you are not authorized to use it".

Which Oracle username does your ODBC connection use to log in to Oracle?  Which Oracle user (or schema) actually owns the table (or view) that you are trying to use?  If they are not the same, check with the person responsible for the Oracle user account that owns the table to see if he/she can give your Oracle login account permission to use the table.

Or, maybe the permission has been granted, but the problem is a missing synonym.

yes, as markgeer pointed out it may be permissions.
try using all_tables instead of dba_tables.

note, you won't be getting as complete a list doing that though.

if you really need every table, then you'll need to use a privileged account or have your dba do it for you.
rollerbl gal20Commented:
Hello, stdstuber,

What is the  '/ROWSET/ROW/X' in your script(following)? What should I replace this with mine?

                        xmltype(DBMS_XMLGEN.getxml(   'select count( distinct '
                                                   || column_name
                                                   || ') X from '
                                                   || table_name)),
Mark GeerlingsDatabase AdministratorCommented:
To  rollerbl gal20:

You should post your question as your own question.  In it, you can refer to this question if you want to, with this URL:

I will advise you that trying to use XML to build a dynamic query that will count the number of distinct column values for every column of every table in your database, and then running those queries, is certainly *NOT* a way to get this done quickly!  Depending on the size of your database and the speed of your storage system and your CPUs, you may be waiting hours or days for something like this to finish.

If you have table statistics being collected regularly  (as most Oracle databases should have) you can query the: owner, table_name, and num_rows columns of the all_tables view *VERY* quickly.  And, you can also query the: owner, table_name, column_name and num_distinct columns of all_tab_columns quite quickly.

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now