Link to home
Start Free TrialLog in
Avatar of johnnywong71
johnnywong71

asked on

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.
Thanks!

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

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


ASKER CERTIFIED SOLUTION
Avatar of anand_2000v
anand_2000v
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Sean Stuber
Sean Stuber

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,
         column_name,
         TO_NUMBER(EXTRACTVALUE(
                       xmltype(DBMS_XMLGEN.getxml(   'select count( distinct '
                                                  || column_name
                                                  || ') X from '
                                                  || table_name)),
                       '/ROWSET/ROW/X'
                   ))
             COUNT
  FROM   user_tab_cols
 WHERE   data_type IN ('NUMBER', 'VARCHAR2', 'CHAR', 'DATE');
note you can get approximations of counts with statistics also

select table_name,column_name, num_distinct from user_tab_cols
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.
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.
Avatar of johnnywong71

ASKER

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
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.
Hello, stdstuber,

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


TO_NUMBER(EXTRACTVALUE(
                        xmltype(DBMS_XMLGEN.getxml(   'select count( distinct '
                                                   || column_name
                                                   || ') X from '
                                                   || table_name)),
                        '/ROWSET/ROW/X'
                    ))
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:
https://www.experts-exchange.com/questions/24170132/List-tables-with-count-of-rows-in-Oracle-database.html?anchorAnswerId=41552159#a41552159

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.