Is there a way to in Oracle to see what tables have data and which don't?

Question

I want know if there is a way to search a database and find out which tables are empty and which have data. I will be migrating some data to another system and it would be nice to know which tables I should export. I'm using Oracle SQL Developer.

Answer 1

One way to do it, aside from running a silly pl/sql block to count(*) for each table, is to run this:

SELECT num_rows FROM ALL_TAB_STATISTICS WHERE OWNER = 'user name';

(Alternate tables: DBA_TAB_STATISTICS, USER_TAB_STATISTICS)

But then, it's valid only if you recently gathered statistics with the DBMS_STATS package.

Answer 2

Yes, You can select count for all tables in a database with a query like

select table_name,
to_number(
   extractvalue(
      xmltype(
         dbms_xmlgen.getxml('select count(*) c from '||table_name))
,'/ROWSET/ROW/C')) count
from user_tables;

Heres a demo